Forum Discussion
Find missing/blank values from two specific columns for two tables that are linked by "bridge table"
Hi,
My task is to compare the values in two fact tables that are linked by a bridge table and find missing information based on the missing primary key.
For instance, table A has data for one primary key but table B doesn't and vice versa and what would be the best way to visually flag these.
Thanks
Hi Klaud ,
I created some data:
Main_Table：
TableA:
TableB:
Here are the steps you can follow：
1. Create measure.
Measure = var _columnA= SELECTCOLUMNS('TableB',"DateB",[Date]) var _columnmain= SELECTCOLUMNS( FILTER(ALL(Main_Table), 'Main_Table'[Date] in _columnA),"Indexmain",'Main_Table'[Index]) return IF( MAX('TableA'[Index]) in _columnmain,"red")2. Select TableA [Group] – Conditional formatting – Background color.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Klaud , Create two measures
M1 = countrows(Table1)
M2 = countrows(Table2)
Now you can count missing in table 1 for a give dim like
countx(Values(DIm[Key]), if(isblank([M1]), [Key], Blank()) )
for Table 2
countx(Values(DIm[Key]), if(isblank([M2]), [Key], Blank()) )
Logic would be similar to
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529But you have measures from different tables
Hi Klaud ,
I created some data:
Main_Table：
TableA:
TableB:
Here are the steps you can follow：
1. Create measure.
Measure = var _columnA= SELECTCOLUMNS('TableB',"DateB",[Date]) var _columnmain= SELECTCOLUMNS( FILTER(ALL(Main_Table), 'Main_Table'[Date] in _columnA),"Indexmain",'Main_Table'[Index]) return IF( MAX('TableA'[Index]) in _columnmain,"red")2. Select TableA [Group] – Conditional formatting – Background color.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thanks!
Exactly the answer I was looking for. Appreciate it 🙂
