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.
Good Morning All,
I'm trying to add a filter to a report for a unit type but am unsure about the best way to accomplish this.
I have 3 unit types Kg, L and USG. My originial report only comes with Kgs, so I've created calculated columns for L and USG. I also have a few measures which calculate Revenue per unit, These measures are the value fields in my report. I want to be able to filter my report by the specific unit type.
I know I can't add a measure to a filter. Any recommendations on how I can achieve this?
Best,
Espinoza
Solved! Go to Solution.
Hey,
I'm very sorry, I was in a little hurry and my DAX statement was flawed - I'm really sorry ...
Nevertheless, here you will find a little example
The statement DAX statement should look like this
Amount to use = IF(HASONEVALUE(UnitSelector[Units]), SWITCH(VALUES(UnitSelector[Units]) ,"10", [Amount times 10] ,"100", [Amount times 100] ,"1000", [Amount times 1000]) ,CALCULATE( SUM(veryverysimplefact[Amount]) ) )
When there is no unit selected (another saying could be - consider all) then the measure from the fact table is used. Otherwise use the appropriate measure, I already defined these measures to avoid clutter in the SWITCH function.
There is also a variation of the above Measure that uses the ALLSELECTED function, as long as there is one Unit selected both measures work the same, but if no Unit is selected in the slicer, the measure using ALLSELECTED returns the error you mentioned in your first post.
Hope this helps
will this help?
http://visualbi.com/blogs/powerbi/dynamic-measure-selection-power-bi/
Proud to be a Super User!
Thanks @vanessafvg,
I'm trying to create the table but I'm getting an error. This is what I have so far.
Unit Measure = SWITCH(ALLSELECTED(Unit_Table[Units]), "Kg",DIVIDE([Total Sales],[Total Kg],0), "L",DIVIDE([Total Sales],[Total L],0), "USG",DIVIDE([Total Sales],[Total USG],0))
Hey,
The error occurs because of the fact that ALLSELECTED() returns a table.
Try the following
SWITCH(
IF(
HASONEVALUE('Unit_Table'[Units])
,VALUES('Unit_Table'[Units])
,"YourDefaultUnit")
, "Kg", ...
,...)
Be aware that VALUES() also returns a table from a single column, but in case that the table just contains a single row, the result of VALUE will implicity converted to skalar value if needed. This is needed by the SWITCH() function.
Hope this helps
Hi @TomMartens,
The syntax only returns one value. And I'm not completely sure of how to incorporate the measure.
Unit Measure 2 = SWITCH( IF( HASONEVALUE(Unit_Table[Units]) ,VALUES(Unit_Table[Units]) ,"Kg") ,"Kg", "L", "USG")
Hey,
I'm very sorry, I was in a little hurry and my DAX statement was flawed - I'm really sorry ...
Nevertheless, here you will find a little example
The statement DAX statement should look like this
Amount to use = IF(HASONEVALUE(UnitSelector[Units]), SWITCH(VALUES(UnitSelector[Units]) ,"10", [Amount times 10] ,"100", [Amount times 100] ,"1000", [Amount times 1000]) ,CALCULATE( SUM(veryverysimplefact[Amount]) ) )
When there is no unit selected (another saying could be - consider all) then the measure from the fact table is used. Otherwise use the appropriate measure, I already defined these measures to avoid clutter in the SWITCH function.
There is also a variation of the above Measure that uses the ALLSELECTED function, as long as there is one Unit selected both measures work the same, but if no Unit is selected in the slicer, the measure using ALLSELECTED returns the error you mentioned in your first post.
Hope this helps
Hi @espinozan,
You should create a new table only including one column(Kg,L,USG), then create the measure using the formula in your fact table rather than the new table.
Add the [Units] as slicer, then you select different value in slicer, you will get different value.
Best Regards,
Angelia
I've tried changing it to the measure but im still getting the same error. Not sure how to fix it.
Unit Measure 1 = SWITCH(ALLSELECTED(Unit_Table[Units]), "Kg",Sales[$ per Kg Price], "L",Sales[$ per L Price], "USG",Sales[$ per USG Price])
can you not create the measure (divide etc) before the switch statement? (not sure if thats the problem but jsut havign a guess here)
Proud to be a Super User!
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |