Count blanks in a column where dates are missing using measure

[Anonymous]

We have table1 in which we are showing  "Mahar Ali" attendance.
We have to count that days in which "Mahar" is absent (as shown in table 2) but we have to create a measure or custom column.
In screenshot i am using (show item with no data) but this has to be done without using of (show item with no data).

PunchInDate,Employee columns are from FACT_TABLE
Date,Days columns are from Dim_Date

Please Help!!!

[Ashish_Mathur]

Hi,

Write this measure and drag it to a card visual

Emp count = counta(fact_table[EmpID])

Absent = countrows(filter(values('DimDate'[Date]),[Emp count]=0))

Hope this helps.

[Anonymous]

thanks for the help ashish , it is working but i want to exclude weekends also.

[Ashish_Mathur · Accepted solution]

Hi,

In the DimDate table, write this calculated column formula

Weekday = weekday(Calendar[Date],2)

Revised the second measure to:

Absent = countrows(filter(values('DimDate'[Date]),[Emp count]=0&&'DimDate'[Weekday]<=5))

[Anonymous]

Thanks for the reply, i have tried this before but it is not working .
Because after && it will accept measure not calculated column.

[Ashish_Mathur]

Hi,

Does this measure work?

Absent = countrows(filter(values('DimDate'[Date]),[Emp count]=0&&weekday(Calendar[Date],2)<=5))

[Anonymous]

it is working now thank you !!!!!!!!

[Ashish_Mathur]

You are welcome.