Forum Discussion
Find differences in related records
I have a dimension table with a unique key column and a value column.
There is a one-to-many relationship with a facts table.
In the facts table, the value is sometimes incorrect.
How can I visualize all but only the records where the value in the facts table doesn't match the one in the dimension table?
It should show the key, the good value (from dim table) and the bad value (from facts table where the key matches but not the value, highlighted in red):
I thought this was quite easy to achieve, but I must be missing some important point.
Hi, Anonymous
You can create a measure and use it in filter pane to show the reault you want.
Like this:
Measure = var a=SELECTEDVALUE('Dimension'[Value]) var b=SELECTEDVALUE(Facts[Value]) return IF(a=b,0,1)Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey
4 Replies
- amitchandak
Super User
Anonymous , You can create a new column in fact
if(related(Dimension[Value]) <> Fact[Value], 1, 0) and then you can use this as flag in visual
amitchandak thanks for the prompt response! I already got to that point using a measure in row context, but what I struggle with is showing the incorrect value (from the facts table) next to the correct value in the dimension table. Will try with a column instead and let you know the outcome.
- v-janeyg-msft
Community Support
Hi, Anonymous
You can create a measure and use it in filter pane to show the reault you want.
Like this:
Measure = var a=SELECTEDVALUE('Dimension'[Value]) var b=SELECTEDVALUE(Facts[Value]) return IF(a=b,0,1)Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
Best Regards,
Community Support Team _ Janey amitchandak this didn't work either. As soon as I include the 'incorrect' value from the facts table in the visual, it returns nonsense. Probably because my dimensions table covers only a portion of the facts table records. So I resorted to a left outer join merge to add the column with the incorrect values in order to display the correct and the incorrect values side by side in the same visual.
