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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

@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
Solution Sage
Solution Sage

@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
Solution Sage
Solution Sage

@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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.