Measure to Count missing values from lookup table

[Anonymous]

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

[v-janeyg-msft · Accepted solution]

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.