cancel
Showing results 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

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

 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

1 ACCEPTED SOLUTION
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.
4 REPLIES 4
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.
Helper V

hi @BeaBF

thankyou for sharing the approach...

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.

Helper V

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

Kind regards

sameer

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors