Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have 3 categories of clients (Column 1), each starting with a different number.
I need to be able to calculate how many in each category are Blank in Column 2.
I have been able to filter to get totals of categories separately (filtering for 1st number), and to count the total Blanks for the entire table, but I cannot figure out how to create a measure that tells me Blank within each category.
Thank you!
Hi @BankJay ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
It would be better if the expected results were shown in the form of pictures.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Here is an example. So here, I need to know how many blanks in "Name" for each ID category starting with a different number. For example, for "ID" starting with "5" how many blanks. For "ID" starting with "8" how many blanks. For "ID" starting with "9" how many blanks.
Hi @BankJay ,
I created some data:
Here are the steps you can follow:
1. Create calculated column.
Column =
COUNTX(
FILTER(ALL('Table'),
'Table'[Column1]=EARLIER('Table'[Column1])&&'Table'[Column2]=BLANK()),[Column1])
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you, but I'm not sure if that quite gets to what I need. In your example (thank you for creating data!) I would need one additional step I think. I would need to calculate a measure for Category 123, Category 234, and Category 345 that just tells me the total blanks.
Is there an easy way to combine that with what you have shown?
Have you looked at the COUNTBLANK function? https://learn.microsoft.com/en-us/dax/countblank-function-dax
Thank you for this reply! That would work, but I'd have to find a way to form that function for each of my Categories, which all appear in Column 1. Any ideas?
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
87 | |
59 | |
52 | |
38 | |
21 |