Forum Discussion

amcqueennes's avatar
amcqueennes
Regular Visitor
8 years ago
Solved

Cumulative Totals using inactive relationship

  Hi everyone,   Firstly please excuse my ignorance; I am very new to Power BI and DAX and am just learning the ropes.    I have a model which has one table with a number of measures and dates. ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    amcqueennes,

    In your scenario, DATE_NAME column is Date type and SDCCN_DIM_DATE_ID is whole number type, also you create inactive relationship as shown in the following screenshot, right?

     


    If so, please change your DAX to the following:

    Total Cumulative by inactive relationship = CALCULATE(SUM(SDCCN_ENROL_ANALYSIS_TEST[IS_ENROLMENT]),USERELATIONSHIP(SDCCN_ENROL_ANALYSIS_TEST[ENROL_DATE_KEY],SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID]),FILTER(ALLSELECTED(SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID]),SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID] <= MAX(SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID])))


    Then we can get the result what we want. Obviously, it is different with the active one.


    For more information, please check the pbix as attached.

     

    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
8 years ago

amcqueennes,

In your scenario, DATE_NAME column is Date type and SDCCN_DIM_DATE_ID is whole number type, also you create inactive relationship as shown in the following screenshot, right?

 


If so, please change your DAX to the following:

Total Cumulative by inactive relationship = CALCULATE(SUM(SDCCN_ENROL_ANALYSIS_TEST[IS_ENROLMENT]),USERELATIONSHIP(SDCCN_ENROL_ANALYSIS_TEST[ENROL_DATE_KEY],SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID]),FILTER(ALLSELECTED(SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID]),SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID] <= MAX(SDCCN_DIM_TIME[SDCCN_DIM_DATE_ID])))


Then we can get the result what we want. Obviously, it is different with the active one.


For more information, please check the pbix as attached.

 

Regards,
Lydia