cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amcqueennes Visitor
Visitor

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Cumulative Totals using inactive relationship

@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?
1.png

 


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.
2.png

For more information, please check the pbix as attached.

 

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: Cumulative Totals using inactive relationship

@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?
1.png

 


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.
2.png

For more information, please check the pbix as attached.

 

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.