Forum Discussion
Comparing a selected Value from one table to All values in another table.
Hi Power Bi community,
I am relatively new to Power bi, I feel like this should be simple but i cannot figure it out.
Essentially I am trying to compare a single selected value in one table against all values in another table.
So for example
Table 1
| prices |
| 100 |
| 200 |
| 300 |
| 400 |
| 500 |
| 600 |
| 700 |
Table 2
| Thresholds |
| 100 |
| 150 |
| 200 |
| 250 |
Outcome:
If i select 100 in thresholds, it compares all prices to 100 and marks 1 if its above or 0 if its below. If i select 150 it compares all the values to 150 and marks 1 if its above or 0 if its below, and so on.
So for an example outcome (assuming here i have selected 150)
| prices | Check |
| 100 | 0 |
| 200 | 1 |
| 300 | 1 |
| 400 | 1 |
| 500 | 1 |
| 600 | 1 |
| 700 | 1 |
Quick note as well,
I am also using DirectQuery for table 1 and imported an excel sheet for thresholds.
Thank you all in advance for the help!
Hi Macks ,
You can try this expression to reach that.
check = IF ( ISBLANK ( SELECTEDVALUE ( Table1[prices] ) ), "Please select one value", IF ( SELECTEDVALUE ( Table2[Thresholds] ) > SELECTEDVALUE ( Table1[prices] ), 1, 0 ) )Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Macks ,
You can try this expression to reach that.
check = IF ( ISBLANK ( SELECTEDVALUE ( Table1[prices] ) ), "Please select one value", IF ( SELECTEDVALUE ( Table2[Thresholds] ) > SELECTEDVALUE ( Table1[prices] ), 1, 0 ) )Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello!
I tried this using a calculated column, I had to use Related on table2(thresholds), if i dont, it says: expressions that yield variant data type cannot be used to define calculated columns
So when i use Related,
It states that my Threshold table doesnt exist or doesnt have a relationship to any table availabe in the current context. My current relation from thresholds to price is one to many (one threshold relates to many prices).
Also as a quick side note, i searched SELECTEDVALUE here: SELECTEDVALUE function - DAX | Microsoft Docs, and it says
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules
Is there a work around for all this?
sorry for all the questions!
Hi Macks ,
If you use a calculated column, then there is no way to select threshold. selectedvalue does not make sense when used in a calculated column. Actions at the report level are not fed back to the data table.
A workround for selectedvalue() is max(). MAX() is supported for use in DirectQuery.
Please share that your pbix file does not contain sensitive information. and the expected results. I apologise as I am not entirely understanding why you need to use related?
Best Regards
Community Support Team _ chenwu zhu
