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
Anonymous
Not applicable

Combining measures with diffent calendars

Hello,

 

I have a table with different dates. The table includes columns for print date, close date and modified date . To generate reports with related values with these dates, I have created a Print date Calendar, close date calendar and a modified date calendar. In this way I am able to calculate for example the measure Sales per print date. 

As each calendar is linked to a certain measure I am not able to combine these measure using one calendar.

Print date are actual dates whereas close dates are dates in the future.

 

My goal is to have one table with one calendar in in which all measures are shown.

 

Thanks

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi Robbe,

 

I came also across this solution https://carldesouza.com/power-bi-dax-userelationship-multiple-dates/ Is this what you mean?

So creating new measures using a non active relationship?

 

 

View solution in original post

4 REPLIES 4
RobbeVL
Impactful Individual
Impactful Individual

Hi there,

 

This is possible using 1 Date Dimension.
You can create the extra relationships in your model, they will automatically become NON-Active relationships.
Not active doesnt mean you cannot activate them in a measure.

 

ex. In a Calculate measure you can do the following:
CALCULATE( SUM(SALES); printdate = "2018"; USERELATIONSHIP(DATEKEY,PRINTDATE))

This will use the required relationship.

 

I hope this helps!

 

Robbe

Anonymous
Not applicable

Hi Robbe,

 

I came also across this solution https://carldesouza.com/power-bi-dax-userelationship-multiple-dates/ Is this what you mean?

So creating new measures using a non active relationship?

 

 

That's exactly the solution the both of us suggested, yes. 

 

If you have further problems implementing it, feel free to follow up here.

Cmcmahan
Resident Rockstar
Resident Rockstar

So the first step would be to just create one date dimension table and relate it to all 3 columns.  Set whichever one you would use by default most often as the 'active' relationship, the other two will be inactive.

 

Then in your measures, you can use USERELATIONSHIP to switch the active relationship in a measure.  For example, if you defaulted to Print Date, but wanted to sum groups based on the Close Date, you could write an expression like this one.  You can read more about it here

SumClosed = CALCULATE( SUM(Table1[SummableValue]), USERELATIONSHIP( Table1[CloseDate], Calendar[DateKey]))

There's also the option of using TREATAS instead, which can be useful for more complicated relationships.  Further reading can be found here

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.