Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
@bdeleur ,
gepland =
sumx(values(date[Date]), calculate(DISTINCTCOUNTNOBLANK(bic_hmy_ingeplande_activiteit[Naam_Medewerker])) )
assuming date from date table is used in visual
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
90 | |
84 | |
76 | |
64 |
User | Count |
---|---|
145 | |
109 | |
109 | |
102 | |
96 |