DAX to calculate the count of flags based on filter selection

[deb_power123]

Hi All,

In the below school table and 3 drop downs location, schoolname and relativedates, I need to find the count of the flags 1 and 0 when I select the location and school name in the drop down and select the relative dates in a relative filter for date column which filters  This month, previous month valuesetc.

So, based on these three filters I should get the count of the flags 1 and 0 and display the values in the scorecard visual.

Here 0 stands for the students who reserved seat and were present in schoolcompetition and 1 stands for students who didn't reserve seats but were present in the schoolcompetition.

Expected results: My score card visual should show the count values for both scenario 1 and scenario 2

Scenario1 :

Select location as chennai and school name as A and relative date as Last month then the total flag count for values (0 and 1) should display 4 in the scorecard visual as there are 4 row counts in the selected filter range.

Scenario 2: 

Select location as Delhi and school name as B  in the filter dropdown and select relative date  filter as This month(present month) then the flag count for (0) should return. so in this case it should display 4 row count in the  scorecard visual in the selected filter range.

Can you please suggest any DAX to handle this case scenario?

Input Source data 

Date	SchoolName	Location	Flag	StudentID
02.08.2021	A	Chennai	1	100
02.08.2021	A	Chennai	1	101
10.08.2021	A	Chennai	0	102
12.08.2021	A	Chennai	1	103
24.08.2021	B	Delhi	0	104
16.09.2021	B	Delhi	0	105
16.09.2021	B	Delhi	1	106
16.09.2021	B	Delhi	0	107
18.09.2021	B	Delhi	1	108
21.09.2021	B	Delhi	0	109
22.09.2021	B	Delhi	0	110
22.09.2021	B	Delhi	1	120

kind regards,

Sameer

[BeaBF · Accepted solution]

@deb_power123 Sure! My table name is SCHOOL, fit it with yours.

Measure that count both 0s and 1s:

COUNT_FLAG = CALCULATE(COUNT(SCHOOL[ Flag]), ALLSELECTED(SCHOOL[ Location], SCHOOL[Date], SCHOOL[SchoolName]))

Measure that count only 1s:

COUNT_FLAG_1 = CALCULATE(COUNT(SCHOOL[ Flag]), SCHOOL[ Flag] <> 0, ALLEXCEPT(SCHOOL, SCHOOL[ Location], SCHOOL[SchoolName], SCHOOL[Date]))

Measure that count only 0s:

COUNT_FLAG_0 = CALCULATE(COUNT(SCHOOL[ Flag]), SCHOOL[ Flag] <> 1, ALLEXCEPT(SCHOOL, SCHOOL[ Location], SCHOOL[SchoolName], SCHOOL[Date]))

Are they okay?

B.

[deb_power123]

hi @BeaBF 

thankyou for sharing the approach...

[BeaBF]

@deb_power123  I didn't understand if you want the joint count of both 0s and 1s together or just one of the two types.

B.

[deb_power123]

Hi @BeaBF  

I want two scorecards, one displaying the joint counts of 0 and 1 flags and the other dispalying just the count of one flag 1..

Could you please suggest?

Kind regards

sameer