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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
deb_power123
Helper V
Helper V

DAX to calculate the count of flags based on filter selection

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 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 

DateSchoolName 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

1 ACCEPTED SOLUTION
BeaBF
Impactful Individual
Impactful Individual

@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.

View solution in original post

4 REPLIES 4
BeaBF
Impactful Individual
Impactful Individual

@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.

hi @BeaBF 

thankyou for sharing the approach...

BeaBF
Impactful Individual
Impactful Individual

@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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors