The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Now, I now have the difficulty of the figures not changing when the slicer is activated, as shown below
Cheers in advance
Pingi
Solved! Go to 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
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
@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/
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
11 | |
7 |