Forum Discussion
Comparing 2 datasets for "missing" data
Hi Jo1963
Normally you pull in your Row and Column fields from the Lookup Table.
In this case the missing "N/A#" equivalents will show up as blank like this
So you could set up a Pivot Table with a Filter on for Product Name = Blank and also showing Product Code from the Fact / Transaction table. Then if anything is missing it will display.
You could also add a calculated column to your Fact table =ISBLANK( RELATED( ProductTable[Product Name] ) ) and it will show TRUEs wherever it's missing.
Let me know if that does / doesn't make sense
Cheers
Hope that helps
Wyn
MVP
Australia UTC+ 8
If this answer was the best response please click the button
I also happily accept likes
Hi Jo1963
Normally you pull in your Row and Column fields from the Lookup Table.
In this case the missing "N/A#" equivalents will show up as blank like this
So you could set up a Pivot Table with a Filter on for Product Name = Blank and also showing Product Code from the Fact / Transaction table. Then if anything is missing it will display.
You could also add a calculated column to your Fact table =ISBLANK( RELATED( ProductTable[Product Name] ) ) and it will show TRUEs wherever it's missing.
Let me know if that does / doesn't make sense
Cheers
Hope that helps
Wyn
MVP
Australia UTC+ 8
If this answer was the best response please click the button
I also happily accept likes
Wyn Hopkins- so I must be doing something wrong with the isblank formula. All I am getting is FALSE on my fact table & I know this is not correct. I have attached a simplified version (and put the reference and fact tables on one sheet - can you advise
Calculated column is to be added in data model, not in source table
Oh of course SergeiBaklan - thank you for that 🙂
Wyn Hopkins- thank you ! I'm using the ISBLANK formula & it works like a charm 🙂
