Count only the not blanks and summarize in table

[bdeleur]

Hi,

When I want to count the not blanks in my table and only return the unique values I get it done with: 

gepland = DISTINCTCOUNTNOBLANK(bic_hmy_ingeplande_activiteit[Naam_Medewerker]))

But in the table in the end row it doesnt count all the values.

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.

[v-stephen-msft]

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.

[bdeleur · Accepted 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.

[amitchandak]

@bdeleur ,

gepland =

sumx(values(date[Date]), calculate(DISTINCTCOUNTNOBLANK(bic_hmy_ingeplande_activiteit[Naam_Medewerker])) )

assuming date from date table is used in visual

[bdeleur]

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.