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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.