cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
espinozan Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

Re: Filtering by Unit type

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
vanessafvg Super Contributor
Super Contributor

Re: Filtering by Unit type

@espinozan

 

will this help?

 

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


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
espinozan Regular Visitor
Regular Visitor

Re: Filtering by Unit type

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  

 

vanessafvg Super Contributor
Super Contributor

Re: Filtering by Unit type

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


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
v-huizhn-msft Super Contributor
Super Contributor

Re: Filtering by Unit type

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

Super User
Super User

Re: Filtering by Unit type

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
espinozan Regular Visitor
Regular Visitor

Re: Filtering by Unit type

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 Regular Visitor
Regular Visitor

Re: Filtering by Unit type

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

Super User
Super User

Re: Filtering by Unit type

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 70 members 2,458 guests
Please welcome our newest community members: