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

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

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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