Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello dear PowerBI Community,
I have the following request from a user and would appreciate your help.
The user would like to be able to filter a visual in the following table:
Item | Item Value | Item Quantity |
Item 1 | 49 | 100 |
Item 2 | 100 | 1 |
Item 3 | 20 | 1000 |
if the value of an item is greater than x
or
if the quantity is greater than Y, value does not matter
For example:
Itemvalue >50
quantity >500
He wants to see Item 2 and Item 3
I hope you can help me!
Thank you
Stewwe
Solved! Go to Solution.
Hi @Stewwe ,
First create 2 dim tables as below:
dim1 = GENERATESERIES(1,MAX('Table'[Item Quantity]),1)
dim2 = GENERATESERIES(1,MAX('Table'[Item Value]),1)
Then create a measure as below:
Measure = IF(SUM('Table'[Item Quantity])>SELECTEDVALUE(dim1[quantity])||SUM('Table'[Item Value])>SELECTEDVALUE(dim2[Value]),1,BLANK())
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Hi @Stewwe ,
First create 2 dim tables as below:
dim1 = GENERATESERIES(1,MAX('Table'[Item Quantity]),1)
dim2 = GENERATESERIES(1,MAX('Table'[Item Value]),1)
Then create a measure as below:
Measure = IF(SUM('Table'[Item Quantity])>SELECTEDVALUE(dim1[quantity])||SUM('Table'[Item Value])>SELECTEDVALUE(dim2[Value]),1,BLANK())
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
Thank you! Exactly what I was looking for. 🙂
@Stewwe , Try a measure or all measures should follow
calculate(sum(Table[quantity]), filter(Table,Table[quantity] >500 || Table[Itemvalue] >50))
Thank you @amitchandak for your solution.
However, the user should be able to set the filter values dynamically and not store fixed values in the formula.
That is the challenge 😉
Bye
Stewwe
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
96 | |
90 | |
79 | |
67 |
User | Count |
---|---|
153 | |
125 | |
114 | |
111 | |
95 |