Forum Discussion
Measure to Count missing values from lookup table
Hi There,
I have put together a PnL in Power BI through a BC connection.
In order to get the correct Sort Order/Layout and Sub Headers, I needed to create an Excel Lookup Table that is related to my Chart Of Accounts Table as below:
Extract of Table: COA Sort
| Account Category Sort | Account_Category | Account_Subcategory_Descript | Account SubCategory Sort |
| 1 | Income | Sales | 1 |
| 3 | Cost of Goods Sold | Stock Purchases | 3 |
| 5 | Expense | Marketing | 10 |
| 5 | Expense | Office Costs | 17 |
Relationship Between COA Sort and Chart of Accounts: Account_Subcategory_Descript
Now, the issue will come in when a new SubCategory Description is added in the future and will show up as a blank line on the PnL.
To overcome this, I want to place a Card in the report that is a DAX measure that will count any missing values from the COA Sort table. (I will then put an alert on this to email the user when it changes from 0)
What would be the correct way to write this measure so it will count the blank/missing values from my lookup table?
Thank you in advance.
Kind Regards,
Max
Hi, Anonymous
If I understand what you mean, I think you can create a measure.
Like this:
measure = COUNTROWS ( DISTINCT ( tablemain[Account_Subcategory_Descript] ) ) - COUNTROWS ( DISTINCT ( tablesub[Account_Subcategory_Descript] ) )If it doesn't solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi, Anonymous
If I understand what you mean, I think you can create a measure.
Like this:
measure = COUNTROWS ( DISTINCT ( tablemain[Account_Subcategory_Descript] ) ) - COUNTROWS ( DISTINCT ( tablesub[Account_Subcategory_Descript] ) )If it doesn't solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.