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 measure to find distinct count of flags set to '1' and '0'

Hi Experts,

 

I have a table say employee which has a attendance flag say flag set to 1 or 0 stating whether they attended the workplace or not. I need to calculate the disctinct count of the flags which are set to 1 or 0  .

 

DAX measure should calculate count(attended) and count(not attended) and % age not attended = count(not attended)/count(attended) * 100 .

 

Empid Flag date
101115.09.2021
102115.09.2021
103015.09.2021
104015.09.2021
101116.09.2021
102016.09.2021
105116.09.2021
103116.09.2021

 

Could you please suggest a DAX to handle this scenario?

 

Kind regards

Sameer

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@deb_power123 Not entirely clear, if you want distinct employees maybe:

Measure Flag is 1 = 
  COUNTROWS(
    DISTINCT(
      SELECTCOLUMNS(
        FILTER('Table',[Flag] = 1),
        "Empid",[Empid]
      )
    )
  )

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@deb_power123 Not entirely clear, if you want distinct employees maybe:

Measure Flag is 1 = 
  COUNTROWS(
    DISTINCT(
      SELECTCOLUMNS(
        FILTER('Table',[Flag] = 1),
        "Empid",[Empid]
      )
    )
  )

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
VahidDM
Super User
Super User

Hi @deb_power123 

 

Try this measure:

% age not attended = 
VAR _Att =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[ Flag] = 1 )
VAR _NAtt =
    CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[ Flag] = 0 )
RETURN
    _NAtt / _Att * 100

 

Output:

VahidDM_0-1631790620591.png

 

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos🙏!!

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