Measure creation to compare values from another table

[Anonymous]

Hi Team,

I have two tables, Fact and dimension tables. Both tables having 'level' (int) column. Dimension table having threashold values for 'level'. I want to create measure where I will get the 'level' from Fact table and compare it with dimension threashold and if the values exceeds the threashold values then I want to show that in the chart.

Thank you in advance!

Regards,

Jyoti Pawar

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, I create a sample.

Fact table:

Dimension table:

Here's my solution, create a measure.

Measure =
IF (
    MAX ( 'Fact'[Value] )
        > MAXX (
            FILTER ( ALL ( 'Dimension' ), 'Dimension'[level] = MAX ( 'Fact'[level] ) ),
            'Dimension'[Threashold]
        ),
    1,
    0
)

Put the measure in the Fact table visual filter and let its value to 1, the Fact table will be filtered fine.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for your answer.