Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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
My apologies! i couldnt open the file for some reason so i assumed you created a calculated column but once i was able to open the pbi i realized you made a measure! I marked it as the accepted solution, thank you so much for the help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.