Based on slicer selection create dynamic calculated table in Power BI

3
votes

I’m new to Power BI. Currently facing similar issue explained below in my product development.

I have created power bi modle with below dimensions and facts from adventureworksDW.

enter image description here

Then I created a calculated table, which gives result as sum of sales group by ProductSubCategory and ProductCategory. Below is the DAX for the calculated table.

enter image description here

enter image description here

enter image description here

Now I want to create a new calculated table, which gives me TOPn ProductSubCategory based on the Total sales amount. Below is the DAX to do this.

enter image description here

and model relationships looks like below.

enter image description here

I want this TOPn rows to be displayed based on filter condition on product category. Something like below.

enter image description here

This works fine when I hardcode the product category value in the DAX itself. But if I want to change this product category values from the slicer selection, then I didn’t get any results.

enter image description here

2
powerbi

2 Answers

0
votes

What you are asking for is not possible as Power BI is currently designed. Slicers cannot affect calculated tables. Calculated columns and calculated tables are evaluated once when the data is first loaded and are static until the data is refreshed.

However, you can get the table visual you want in a much simpler manner by writing the appropriate measure and putting that in the table instead of defining an entirely separate table.

TotalSales = SUM(FactInternetSales[SalesAmount])

The Top N filtering is available in the visual level filters settings.

0
votes

You can simply use the SELECTEDVALUE function as shown below.

var __SelectedValue = SELECTEDVALUE('ProductSales'[EnglishProductCatogaryName])
return
Filter(
    'ProductSales',
    'ProductSales'[EnglishProductCatogaryName] = __SelectedValue 
    )
)