Find missing/blank values from two specific columns for two tables that are linked by "bridge table"

[Klaud]

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

[Anonymous · Accepted solution]

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

[Klaud]

Thanks!

Exactly the answer I was looking for. Appreciate it 🙂

[amitchandak]

@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/b...

But you have measures from different tables