Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |