How to Dynamically change X-Axis and Legends in Power BI

How to select or change a Dynamic X-Axis and a Legend in Power BI
Scenario:

Suppose we have a Data Model as shown below.

Dimension Tables :

tbl_Calendar ; tbl_Country ; tbl_Products ; tbl_Customers

Fact Tables :

tbl_OrderDetails ; tbl_Sales ; tbl_Cost

Based on this Model, we can access the Sales figures from any of the Dimension tables via Orders table. The Relationships are as per below:

Now our requirement is to create a one common Dynamic Slicer with ability to choose any of the Dimensions like Country, Customer or Product to view Sales by that selected Dimension. We can achieve this  Scenario by the following way.

Step 1 :

Create a Dimension Attributes table with required Dimensions for user selection.

DimAttributes =

VAR vCntry = SELECTCOLUMNS(tbl_Country,

"DimID", tbl_Country[Country_Id],

"DimValue", tbl_Country[Country],

"DimName", "Country" )

VAR vCust = SELECTCOLUMNS(tbl_Customers,

"DimID", tbl_Customers[Cust_Id],

"DimValue", tbl_Customers[Cust_Name],

"DimName", "Customer")

VAR vProd = SELECTCOLUMNS(tbl_Products,

"DimID", tbl_Products[Prod_Id],

"DimValue", tbl_Products[Prod_Name],

"DimName", "Product")

RETURN

UNION(vCntry, vCust, vProd)

Step 2 :

Create the In-Active Relationships between newly created DimAttributes table and Dimension tables using the DimID and respective related Id columns in the Dimension tables as shown in below:

Step 3 :

Create a Dynamic Sales measure that should calculate the Sales for the Selected Dimension,

by activating the relationship with selected Dimension table.

Sales(Dynamic) = SWITCH( TRUE(),

--------- by Coutry ------------

SELECTEDVALUE(DimAttributes[DimName])="Country",

CALCULATE([TotalSales],

USERELATIONSHIP(tbl_Country[Country_Id],DimAttributes[DimID])),

--------- by Customer ------------

SELECTEDVALUE(DimAttributes[DimName])="Customer",

CALCULATE([TotalSales],

USERELATIONSHIP(tbl_Customers[Cust_Id],DimAttributes[DimID])),

--------- by Product ------------

SELECTEDVALUE(DimAttributes[DimName])="Product",

CALCULATE([TotalSales],

USERELATIONSHIP(tbl_Products[Prod_ID],DimAttributes[DimID])),

BLANK()

)

Note:

here, [TotalSales] = SUM(tbl_Sales[Gross_Sales])

Final Step :

Dynamic X-Axis Selection (using Stacked Column Chart):

Next create Slicer with DimAttributes[DimName], and build a Visual with DimValue on X-Axis and Sales (Dynamic) measure on the Y-Axis as shown below, I have used the Stacked column chart.

Now if you choose a another Dimension in the Slicer, the chart X-Axis and chart Title also will change as per the selected Dimension.

Note: We can make the chart title Dynamic using the following expression:

Dyn_ChartTitle = VAR SelDim = SELECTEDVALUE(DimAttributes[DimName])

RETURN "Total Sales By "& SelDim

Dynamic Legends Selection (using Stacked Column Chart):

Using same logic and chart, we can dynamically select/change a Legend as per below. In this case, I have used an additional Dimension "QTR_Year" from tbl_Calendar on X-Axis, and the

Sales (Dynamic) measure on Y-Axis and DimValue as Legend which will change dynamically.

Note:

The above Dynamic Sales measure can be written using the TREATAS() Function as well as per below.

In this case, there is no need to establish a In-Active relationships between DimAttributes table and the respective Dimension Tables.

Dynamic_Sales = SWITCH( TRUE(),

--------- by Coutry ------------

SELECTEDVALUE(DimAttributes[DimName])="Country",

CALCULATE([TotalSales],

TREATAS(VALUES(DimAttributes[DimID]),tbl_Country[Country_Id])),

--------- by Customer ------------

SELECTEDVALUE(DimAttributes[DimName])="Customer",

CALCULATE([TotalSales],

TREATAS(VALUES(DimAttributes[DimID]),tbl_Customers[Cust_Id])),

--------- by Product ------------

SELECTEDVALUE(DimAttributes[DimName])="Product",

CALCULATE([TotalSales],

TREATAS(VALUES(DimAttributes[DimID]),tbl_Products[Prod_ID])),

BLANK()

)

# -------------------------------------------- OR ---------------------------------------- #

Dynamic_Sales = IF(HASONEVALUE(DimAttributes[DimName]),

SWITCH( VALUES(DimAttributes[DimName]),

--------- by Coutry ------------

"Country",

CALCULATE([TotalSales],

TREATAS(VALUES(DimAttributes[DimID]),tbl_Country[Country_Id])),

--------- by Customer ------------

"Customer",

CALCULATE([TotalSales],

TREATAS(VALUES(DimAttributes[DimID]),tbl_Customers[Cust_Id])),

--------- by Product ------------

"Product",

CALCULATE([TotalSales],

TREATAS(VALUES(DimAttributes[DimID]),tbl_Products[Prod_ID])),

BLANK() )

)

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------