Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
To all;
I have a real world use case where I'm asked to take multiple slicers, set up as toggles, i.e.
- all data is numeric, could be expressed as text for display (not actual DAX code)
slicers:
Average/Total "Average" = DIVIDE(<data> ,SUM(weeks))
"Total "= SUM(<data>)
Dollars/Units "Dollars" = SUM(<data>)
"Units" = SUM(<data>)
CPIU/ROI "CPIU" = ( return - cost) / units
"ROI" = ( Return - cost)
The basic toggles work well, independently, but my use case needs them to 'nest' or 'cascade'
the slicer slections and related value measures, so that data ex. "Spend" could be filtered by *Both*
Dollars/Units and then Average/Total
The data model is enormous, and I have little control over import of data not included in the original
model.
Any advice, suggestions or reasonably effective magic spells would be deeply appreciated
Thank You !
Joe
This is just me theorizing. You probably have a slicer table to select the correct measures:
| Slicer Measure | Slicer Value |
| Average/Total | 1 |
| Dollars/Units | 2 |
and then some measures that look something like:
AverageSlicer =
IF(
SELECTEDVALUE(
SlicerTable[Slicer Value] = 1
|| ISBLANK(SELECTEDVALUE(SlicerTable[Slicer Value])
),
[Average]
)
TotalSlicer =
IF(
SELECTEDVALUE(
SlicerTable[Slicer Value] = 2
|| ISBLANK(SELECTEDVALUE(SlicerTable[Slicer Value])
),
[Total]
)
I propose something like this:
| Objective Slicer | Slicer Measure | Slicer Value |
| Average/Total | Average/Total | 1 |
| Dollars/Units | Dollars/Units | 2 |
| Both | Average/Total | 3 |
| Both | Dollars/Units | 3 |
AverageSlicer =
IF(
SELECTEDVALUE(
SlicerTable[Slicer Value] = 1
|| SlicerTable[Slicer Value] = 3
|| ISBLANK(SELECTEDVALUE(SlicerTable[Slicer Value])
),
[Average]
)
TotalSlicer =
IF(
SELECTEDVALUE(
SlicerTable[Slicer Value] = 1
|| SlicerTable[Slicer Value] = 3
|| ISBLANK(SELECTEDVALUE(SlicerTable[Slicer Value])
),
[Total]
)
these are some of the mesauses being toggled
Examples:
------------------------------------
Toggle Dollars/Units
I'm not quite sure I understand the situation. How do you have your measures written so that they work independently but not combined?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |