Forum Discussion
Resolver IALL not working
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
2 Replies
- amitchandak
Super User
CarlBlunck , For HR, refer blog and the attached files
refer if needed
Power BI How ALL Works: https://youtu.be/LXgzlZ38mwU
CarlBlunckHi 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
