DAX measure to find distinct count of flags set to '1' and '0'

[deb_power123]

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
101	1	15.09.2021
102	1	15.09.2021
103	0	15.09.2021
104	0	15.09.2021
101	1	16.09.2021
102	0	16.09.2021
105	1	16.09.2021
103	1	16.09.2021

Could you please suggest a DAX to handle this scenario?

Kind regards

Sameer

[Greg_Deckler · Accepted solution]

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

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

[VahidDM]

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:

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

Appreciate your Kudos🙏!!