Power BI: families, creating and splitting apart - 5 of a series of 5 on the DAX TREATAS() function
Art Tennick
??3 US Patents in BI??technical editor 4 Packt Power BI books??author 20 computer books??Power BI??Analysis Services??Python/R in Power BI??Paginated??MDX DAX SQL Python R TMDL??ex-university SQL lecturer??35 years in BI
Last in a series of five. Uses a DAX table and the magic TREATAS() function. To understand, please download the pbix and look at my previous four articles - all five articles show different and useful ways of working with DAX tables and TREATAS(). This example shows how to create a family (a world region and its countries) and separate from other families with a gap in charts and tables (tempted to call it Gap Analysis!). Based on Adventure Works DW, so you can experiment. All the links are next, some of the DAX after that:
LOCATION = FILTER(
UNION(
ADDCOLUMNS(SUMMARIZE(SELECTCOLUMNS(DimGeography, "Dimension", "Location", "Attribute", "Country", "Member", DimGeography[EnglishCountryRegionName], "World region", RELATED(DimSalesTerritory[SalesTerritoryGroup])), [Dimension], [Attribute], [Member], [World region]),
"Sort", [World region] & [Member]),
ADDCOLUMNS(SUMMARIZE(SELECTCOLUMNS(DimGeography, "Dimension", "Location", "Attribute", "World region", "Member", RELATED(DimSalesTerritory[SalesTerritoryGroup]),
"World region", ""), [Dimension], [Attribute], [Member], [World region]),
"Sort", [World region] & [Member]),
ADDCOLUMNS(SUMMARIZE(SELECTCOLUMNS(DimGeography, "Dimension", "Location", "Attribute", "Country", "Member",
REPT(" ", RANKX(DimSalesTerritory, DimSalesTerritory[SalesTerritoryGroup], RELATED(DimSalesTerritory[SalesTerritoryGroup]))), "World region",
RELATED(DimSalesTerritory[SalesTerritoryGroup])), [Dimension], [Attribute], [Member], [World region]), "Sort", [World region] & "zzz")
)
, [Sort] <> TOPN(1, VALUES(DimSalesTerritory[SalesTerritoryGroup]), DimSalesTerritory[SalesTerritoryGroup]) & "zzz"
)
Revenue = SUM(FactInternetSales[SalesAmount])
Revenue1 =
SWITCH(
SELECTEDVALUE(LOCATION[Attribute]),
"Country", CALCULATE([Revenue], TREATAS(VALUES(LOCATION[Member]), DimGeography[EnglishCountryRegionName])),
"World region", CALCULATE([Revenue], TREATAS(VALUES(LOCATION[Member]), DimSalesTerritory[SalesTerritoryGroup])),
[Revenue]
)