Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amcqueennes
Regular 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
v-yuezhe-msft
Employee
Employee

@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.

View solution in original post

1 REPLY 1
v-yuezhe-msft
Employee
Employee

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.