Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bdeleur
Helper III
Helper III

Count only the not blanks and summarize in table

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.
bdeleur_0-1673449320309.png

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.

1 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:

bdeleur_1-1673538526078.png

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.

View solution in original post

4 REPLIES 4
v-stephen-msft
Community Support
Community 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.

vstephenmsft_0-1673503518936.png

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])

 

vstephenmsft_1-1673506280545.png

 

 

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:

bdeleur_1-1673538526078.png

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
Super User
Super User

@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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.