Comparing a selected Value from one table to All values in another table.

[Macks]

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!

[v-chenwuz-msft · Accepted 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.

[Macks]

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!

[v-chenwuz-msft]

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

[Macks]

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!