Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
When I want to count the not blanks in my table and only return the unique values I get it done with:
The outcome must be 15+15+15 = 45.
I think my measure isn't right but I can't get it done. Please help with the right DAX formula.
Solved! Go to Solution.
Hi @v-stephen-msft ,
Yes you have recreated the issue and I recreated your measure and I works but....
It is data out of our plansystem for employees. So 1 shift is divided in server rows. Like this
A 07:00 - 12:00 work
A 12:00 - 12:30 break
A 12:30 - 16:00 work.
You measure counts all the rows an I only want the unique value. So every employee only count 1 time.
The problem is that I have an empty row in the database wich gives the values how much there needs to be plannend. That is the empty row. But when is count the unique rows -1 is isn't a corrrect count.
So simplified it looks like this:
Suddenly I came to a code and it worked:
Telling = COUNTROWS(SUMMARIZE(bic_hmy_ingeplande_activiteit, bic_hmy_ingeplande_activiteit[Datum], bic_hmy_ingeplande_activiteit[Naam_Medewerker]))
Now it gives the correct number a day and the total count.
Tnx for the support.
Hi @bdeleur ,
I think I successfully reproduced your issue.
Because the measure is to count the distinct values, your distinct values on January 1 are 15, the distinct values on January 2 are 15, and the distinct values on January 3 are 15, but your total is also calculated distinct values, that is, the distinct values in three months are 28.
If you still can't understand it, I can narrow down the data, see the sample data below.
Date | Naam_Medewerker |
2023-1-1 | A |
2023-1-1 | B |
2023-1-1 | C |
2023-1-2 | A |
2023-1-2 | B |
2023-1-2 | C |
In the above data, the distinct value of January 1 is 3 (A, B, C), the distinct value of January 2 is also 3 (also A, B, C), and the total distinct value agrees to be 3 (A, B, C).
If you want to return 45 for your sample data, you need to create a new measure based on the original one.
Measure = SUMX('bic_hmy_ingeplande_activiteit[',[planned])
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-stephen-msft ,
Yes you have recreated the issue and I recreated your measure and I works but....
It is data out of our plansystem for employees. So 1 shift is divided in server rows. Like this
A 07:00 - 12:00 work
A 12:00 - 12:30 break
A 12:30 - 16:00 work.
You measure counts all the rows an I only want the unique value. So every employee only count 1 time.
The problem is that I have an empty row in the database wich gives the values how much there needs to be plannend. That is the empty row. But when is count the unique rows -1 is isn't a corrrect count.
So simplified it looks like this:
Suddenly I came to a code and it worked:
Telling = COUNTROWS(SUMMARIZE(bic_hmy_ingeplande_activiteit, bic_hmy_ingeplande_activiteit[Datum], bic_hmy_ingeplande_activiteit[Naam_Medewerker]))
Now it gives the correct number a day and the total count.
Tnx for the support.
Hi @amitchandak,
I'm not using an date table so when I used the date it gives the same result. But I fixed it bij another code.
I will try your code with a date table. I need to learn how that works.
User | Count |
---|---|
129 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
190 | |
97 | |
67 | |
63 | |
54 |