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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
bdeleur
Helper II
Helper II

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.