Filters not changing Figures based on DAX Calculated Tables

[Anonymous]

I am hoping someone can help me.  I needed to perform a transpose function (Columns to Rows) and managed to do so via DAX through the use of UNION and ROW functions.

Commentary = UNION(

ROW("Attribute","Total Sales","Amount",[Ttl Sales]),

ROW("Attribute","Costs","Amount",-[Ttl Costs]),

ROW("Attribute","Profit","Amount",[Ttl Profit]))

Now, I now have the difficulty of the figures not changing when the slicer is activated, as shown below

Cheers in advance

Pingi

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Calculated tables, as in Modeling/New Table/Type stuff in dax, is a "static" table.  It will not respond to filters.

You can create a table to contain Total Sales, Costs, Profit, and be affected by slicers by creating measures.

Here are the steps you can follow：

1. Use Enter to create a table

2. Create measure.

Ttl Sales = SUMX(ALLSELECTED('Main Table'),'Main Table'[AMOUNT])

Ttl Costs =
var _min=MINX(ALLSELECTED('Main Table'),[Date])
var _max=MAXX(ALLSELECTED('Main Table'),[Date])
return
SUMX(FILTER(ALL('Main Table'),'Main Table'[Group]="A"&&'Main Table'[Date]>=_min&&'Main Table'[Date]<=_max),[AMOUNT])

Ttl Profit =
var _min=MINX(ALLSELECTED('Main Table'),[Date])
var _max=MAXX(ALLSELECTED('Main Table'),[Date])
return
SUMX(FILTER(ALL('Main Table'),'Main Table'[Group]="B"&&'Main Table'[Date]>=_min&&'Main Table'[Date]<=_max),[AMOUNT])

Measure =
SWITCH(
    TRUE(),
    MAX('Slice'[Attribute])="Total Sales",[Ttl Sales],
    MAX('Slice'[Attribute])="Costs",[Ttl Costs],
    MAX('Slice'[Attribute])="Profit",[Ttl Profit])

3. Result:

Please click here for the pbix file

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , This is a new table and will not take filter from the old table. My advice would create a calculation group.

Matrix - Show on Row can also help without change

or consider calculation group - Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0 

calculation groups
https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/