Forum Discussion
Measure creation to compare values from another table
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
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
v-yanjiang-msftCommunity Support
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.