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. This table is like an accumulating snapshot table in Kimball's dimensional modelling; it models a process which has a series of steps and at each step we record a date.

This tables links to a date table via these different dates; in power bi one is active while the others are inactive. 

 

I am trying to create a measure which performs a cumulative total using an inactive relationship to the date and I am unsure how this is possible to do. 

 

I have done a cumulative total using an active relationship before like below:

 

Total Cumulative Scheduled By Date =
CALCULATE (
   SUM ( SDCCN_ENROL_ANALYSIS_TEST[IS_SCH_ENROLMENT] ),
   FILTER (
      ALLSELECTED ( SDCCN_DIM_DATE ),
      SDCCN_DIM_DATE[DATE_NAME] <= MAX ( SDCCN_DIM_DATE[DATE_NAME] )
     )
)

 

I have also done creating measures across inactive measures using the USERELATIONSHIP function like below:

 

Total Enrolments =
CALCULATE (
   SUM(SDCCN_ENROL_ANALYSIS_TEST[IS_ENROLMENT]),
   USERELATIONSHIP(
      SDCCN_ENROL_ANALYSIS_TEST[ENROL_DATE_KEY],
      SDCCN_DIM_DATE[SDCCN_DIM_DATE_ID]
   )
)

 

However I have no idea how to combine them. I believe if I wrote the following then the FILTER function would just use the active relationship. 

 

Total Cumulative Enrolment By Date =
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_DATE),
       SDCCN_DIM_DATE[DATE_NAME] <= MAX( SDCCN_DIM_DATE[DATE_NAME])
)

 

 

If anyone has any advice it would be much appreciated!

 

Thanks,

 

Alex

 

  • 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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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