Forum Discussion
Count calculation result
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 ?
Hi,
Try this measure
Measure = COUNTROWS(FILTER(VALUES(Calendrier[Date]),[Solde]==0))
or
Measure = COUNTROWS(FILTER(VALUES(Calendrier[Date]),[Solde]=0&&[Solde]<>blank()))
6 Replies
- v-zhangtiCommunity Support
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.
- Ashish_MathurSuper User
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_SFrequent Visitor
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_MathurSuper User
Hi,
Try this measure
Measure = COUNTROWS(FILTER(VALUES(Calendrier[Date]),[Solde]==0))
or
Measure = COUNTROWS(FILTER(VALUES(Calendrier[Date]),[Solde]=0&&[Solde]<>blank()))