Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Cumulative Measure does not ignore filter despite ALL, while Count Measure does

I have a very simple table showing a Count and a Cumulative of that Count. Somehow the COUNT function does ignore the filter due to the ALL function, the Cumulative does NOT ignore the filter despite of the ALL function.

All relationships work fine. I have a star model running. The table Facts is directly related to the Calendar table and the DIM_Project table. Both one to many, of which the DIM_project is the only bi-directional.

This is the code for the count that I use:

EndedContracts = 
CALCULATE(
    SUM(Facts[Amount]),
    Facts[Type1] = "EndedContract",Facts[Type2] = "SocialRent",
    ALL(DIM_Project))

Now the Cumulative below is using the ALL function, but I actually already expect it already to work without ALL, since the EndedContracts measure already includes it, which I include in this Cumulative EndedContractsCumulative measure. This is the code for the Cumulative that I expect to work, without the ALL function:

EndedContractsCumulative = 
CALCULATE (
   [EndedContracts],
   Filter(
        ALLSELECTED('Calendar'),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date])))

This is the code for the Cumulative that I use and expect to work, with the ALL function:

EndedContractsCumulative = 
CALCULATE (
   [EndedContracts],
   Filter(
        ALLSELECTED('Calendar'),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date])),ALL(DIM_Project))

The results I get are the following. Both with or without the ALL function showing the same results:

EndedContracts  EndedContractsCumulative
37              37
41              78
24              100 --2 are missing due to a filter from table DIM_Project.

When I remove the filter from table DIM_Project the cumulative does work correctly. How to ignore the DIM_Project filter in this Cumulative measure EndedContractsCumulative?

1 Reply

  • Anonymous why you have bi-direction relationship for DIM_Project, share sample data and your relatioship diagrma