Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I'm trying to calculate something and I don't want this value to be changed when a specific filter is applied. I've taken and applied the advice written in this Understanding DAX Auto-Exist - SQLBI blog, but still not having any luck. Here is the measure:
TrendingHeadcount =
VAR EOM = MAX(dim_PlacementDates[Date])
VAR SOM = MIN(dim_PlacementDates[Date])
RETURN
CALCULATE(DISTINCTCOUNT(fact_PlacementData[WamiKey]),
FILTER(fact_PlacementData,
(fact_PlacementData[HireDate]<= EOM && fact_PlacementData[TerminationDate]>=SOM) ||
(fact_PlacementData[HireDate]<= EOM && fact_PlacementData[TerminationDate]=BLANK())
),
ALL(dim_TerminationReasons[TerminationReasonDesc])
)
Here is a snippet of the model where the 3 tables that are referenced in this formula are used:
Any ideas?
Thanks
Carl
Hi @amitchandak ,
Thanks for those. Am applying the logic as suggested but I still can't get it to work 😞
Here is the model showing many-to-one relationships between the fact table and dim tables:
The measure I have used is:
TrendingHeadcount =
VAR EOM = MAX(dim_PlacementDates[Date])
VAR SOM = MIN(dim_PlacementDates[Date])
RETURN
CALCULATE(DISTINCTCOUNT(fact_PlacementData[WamiKey]),
FILTER(fact_PlacementData,
(fact_PlacementData[HireDate]<= EOM && fact_PlacementData[TerminationDate]>=SOM) ||
(fact_PlacementData[HireDate]<= EOM && fact_PlacementData[TerminationDate]=BLANK())
),
ALL(dim_TerminationReasons)
)
Here is the result of the measure with a MonthYear being selected and as you can see, it isn't producing the total against each row.
Then when I select a TerminationReasonDesc in the filter, it just gives me the result for that row and not the total result as desired.
Any ideas what I am doing wrong?
Thanks
Carl
@CarlBlunck , For HR, refer blog and the attached files
refer if needed
Power BI How ALL Works: https://youtu.be/LXgzlZ38mwU
User | Count |
---|---|
81 | |
76 | |
70 | |
69 | |
54 |
User | Count |
---|---|
104 | |
99 | |
92 | |
78 | |
69 |