cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MLay22
Frequent Visitor

Measure to Count missing values from lookup table

Hi There, 

 

I have put together a PnL in Power BI through a BC connection.

 

MLay22_0-1623684028859.png

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 SortAccount_CategoryAccount_Subcategory_DescriptAccount SubCategory Sort
1IncomeSales1
3Cost of Goods SoldStock Purchases3
5ExpenseMarketing10
5ExpenseOffice Costs17

 

Relationship Between COA Sort and Chart of Accounts: Account_Subcategory_Descript

MLay22_1-1623684433158.png

 

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

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

Hi, @MLay22 

 

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.

View solution in original post

1 REPLY 1
v-janeyg-msft
Community Support
Community Support

Hi, @MLay22 

 

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.

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors