Forum Discussion
Helper IIVisual level filter with a conditional "or condition"
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
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,
KellyDid I answer your question? Mark my reply as a solution!
4 Replies
v-kelly-msftCommunity Support
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,
KellyDid I answer your question? Mark my reply as a solution!
StewweThank you! Exactly what I was looking for. 🙂
- amitchandak
Super User
Stewwe , Try a measure or all measures should follow
calculate(sum(Table[quantity]), filter(Table,Table[quantity] >500 || Table[Itemvalue] >50))
- Stewwe
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
