Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
User | Count |
---|---|
106 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |