topic Re: DAX to calculate the count of flags based on filter selection in DAX Commands and Tips

DAX to calculate the count of flags based on filter selection

deb_power123 — Thu, 23 Sep 2021 14:35:54 GMT

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

Re: DAX to calculate the count of flags based on filter selection

BeaBF — Thu, 23 Sep 2021 17:32:24 GMT

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.

Re: DAX to calculate the count of flags based on filter selection

deb_power123 — Fri, 24 Sep 2021 03:12:02 GMT

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

Re: DAX to calculate the count of flags based on filter selection

BeaBF — Fri, 24 Sep 2021 07:45:12 GMT

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?

Re: DAX to calculate the count of flags based on filter selection

deb_power123 — Mon, 27 Sep 2021 11:00:53 GMT

hi BeaBF

thankyou for sharing the approach...