Power BI: let user pick chart x- and y-axis - 1 of a series of 5 on the DAX TREATAS() function

An innovative way to use the DAX TREATAS() - inspired by an article by Kasper de Jonge. Link to his article is at end of this article - also links to the pbix shown in screenshot above and to a live, interactive version. The user can choose what to show on the x-axis of the chart with the slicer on the left. The user can choose what to show on the y-axis with the slicer on the right. Data is from Adventure Works if you want to emulate (or use my pbix). Make sure that Calendar Year in DimDate is set to type text. Next a lot of DAX (impossible to format well in LinkedIn) - the two slicers are DAX tables, quantity is the same as sales:

MEASURE SLICER = UNION(ROW("Measure", "Quantity"), ROW("Measure", "Sales"))

AXIS SLICER = UNION(UNION(

       CROSSJOIN(ROW("Axis", "Country"), VALUES(DimGeography[EnglishCountryRegionName])),

       CROSSJOIN(ROW("Axis", "Product Category"), ALLNOBLANKROW(DimProductCategory[EnglishProductCategoryName]))

   ),

   CROSSJOIN(ROW("Axis", "Year"), VALUES(DimDate[CalendarYear])))

Revenue = SUM(FactInternetSales[SalesAmount])

Sales =

SWITCH(

   SELECTEDVALUE('AXIS SLICER'[Axis]),

       "Country", CALCULATE([Revenue], TREATAS(VALUES('AXIS SLICER'[Attribute]), DimGeography[EnglishCountryRegionName])),

       "Product Category", CALCULATE([Revenue], TREATAS(VALUES('AXIS SLICER'[Attribute]), DimProductCategory[EnglishProductCategoryName])),

       "Year", CALCULATE([Revenue], TREATAS(VALUES('AXIS SLICER'[Attribute]),DimDate[CalendarYear])

   ))

Value =

SWITCH(

   SELECTEDVALUE('MEASURE SLICER'[Measure]),

       "Quantity", [Quantity],

       "Sales", [Sales])

Links

Original Kasper de Jonge article

pbix download

live, interactive version in PowerBI.com