filter data in measure based on multiple columns and measure

[Surya9]

I have 2 tables Employee and Leaves Having relationship Many to many from Employee to Leave based in Empid

Employee

Empid

Status

Enddate

Flg

Leave

Empid

StartDate

Enddate

I had created the measure1 in leave to check whether Empid met the condition or not 
if condition is met then 1 else 0
now i have to create the measure for Employee 
= CALCUALTE ( Distinctcount(Employee[Empid]), 
                          Filter (Employee, 

                                        AND ( AND (Status IN {"IN","Working"},
                                                    ISBLANK (Enddate)),

                                                  OR ( Flg = 0 ,
                                                          Employee[Empid] IN Values( All the Empid from Leave where Measure1 Value is 1))))

Please help me with this or any best approach to achive this

[v-yiruan-msft · Accepted solution]

Hi @Surya9 ,

I created a sample pbix file(see attachment), please check whether that is what you want. You can update your measure as below:

Measure2 = 
VAR _tab =
    CALCULATETABLE ( VALUES ( 'Leave'[Empid] ), FILTER ( 'Leave', [Measure] = 1 ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Employee[Empid] ),
        FILTER (
            Employee,
            AND (
                AND (
                    'Employee'[Status] IN { "IN", "Working" },
                    ISBLANK ( 'Employee'[Enddate] )
                ),
                OR ( 'Employee'[Flg] = 0, Employee[Empid] IN _tab )
            )
        )
    )

Best Regards

[aj1973]

Hi @Surya9 

You request is not quite clear but in my understanding you should use IF statement then DISTINCTCOUNT to get it work.

May be share a Sample Pbix would be better for the community to help you.