Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
espinozan
Helper I
Helper I

Filtering by Unit type

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?

f1.JPG

f2.JPG

 

Best,

Espinoza

1 ACCEPTED 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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

8 REPLIES 8
vanessafvg
Super User
Super User

@espinozan

 

will this help?

 

http://visualbi.com/blogs/powerbi/dynamic-measure-selection-power-bi/





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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))

f3.JPG  

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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")

f5.JPG

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

Hi @v-huizhn-msft

 

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])

f4.JPG

@espinozan

 

can you not create the measure (divide etc) before the switch statement? (not sure if thats the problem but jsut havign a guess here)





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.