Count calculation result

[Thomas_S]

Hello

I'm trying to count the result of a calculated measure but i'm stucked...

I have 2 distinct tables (with many other differents columns, extracted from 2 differents "tools"), looking like that (simplified version) :

Table 1 : 

id      Amount

1       5

2       10

3       8

Table 2 :

id      Amount

1       5

2       2

3       8

From that, 3 calculated measure :

- based on table 1, SUM(amount) by id (let's call it "sum1")

- based on table 2, SUM(amount) by id (let's call it "sum2")

- based on the 2 previous measure, a "sum3" =  "sum2" - "sum1"

The final results by id is :

id      Amount

1       0

2       -8

3       0

From that, i'm trying to count the number of time where the result is = 0 (2 times in my case).

How can I obtain this result ?

[Ashish_Mathur]

Hi,

Drag this measure to a card visual

Measure = countrows(FILTER(VALUES(Allid[id]),[Sum3]=0))

Ensure that you create a seperate table called Allid which has all unique ID's.  Create a relationship (Many to One and Single) of both your data tables to the Allid table.

Hope this helps.

[Thomas_S]

Thanks, we're close to the result !

With this measure : 

Measure = COUNTROWS(FILTER(VALUES(Calendrier[Date]),[Solde]=0))

I got this result :

Which is close to correct. 

In my model, the measure's result I try to test is based on a calendar table (need to count the number of days where "solde" = 0). And in this exemple, from range 03/01 to 08/01, there are 6 days. In this filtered selection, the "solde"= 0 is checked the 06/01. But there are no data the 04/01, so, since the day exist in my calendar, I guess the measure consider the "solde"=0 on this particuliar day. And finally count 2 instead of 1.

What condition / filter can I add to ignore days where there are no datas ?

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

Measure = COUNTROWS(FILTER(VALUES(Calendrier[Date]),[Solde]==0))

or

Measure = COUNTROWS(FILTER(VALUES(Calendrier[Date]),[Solde]=0&&[Solde]<>blank()))

[Thomas_S]

The first one works perfectly ! Thank you !

(the second measure return "empty" on the card)

Last question, for my understanding, why is it working with a double "=" sign in the measure? What does it mean ?

[Ashish_Mathur]

You are welcome.  Read here - DAX operators - DAX | Microsoft Learn

[v-zhangti]

Hi, @Thomas_S 

Measure:

Count = COUNTX(FILTER('Table 1',[Sum3]=0),[Sum3])

Or

Count = COUNTX(FILTER('Table 1',[Sum3]=0),[ID])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.