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

Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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

Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors