Inactive relationships with date table

[SPH76]

I have a really puzzling issue. Working with a PBIX file from third party. It seems they were unaware of how to use USERELATIONSHIP but somehow came up with alternative. 

There is a Fact with 3 INactive relationships to the DimDate table. Nothing special, but NO active relationship. They somehow manage to activate the relationships by using a third (unconnected) table, with a description and an ordinal value for each relationship. 

Example of use: simple quantity from Fact on the y-axis, year & month from DimDate on the x-axis. By default, no result of course, but when the unconnected table is filtered , .... voila! 

Looks like a work-around from days long gone, but I do not see how it works, somehow the unconnected table is affecting the relationships between Fact and DateDim, but no idea how, any ideas? I don't really want to use this, just want to understand how it works 🙂 Unfortunately cannot share the file or extract due to sensitive data.

[SPH76 · Accepted solution]

Figured it out myself, they are calculation groups, sorry about that, had never used them myself and they are completely superfluous here 🙂 but at least it is clear now, @speedramps thnx 4 ur time!

[speedramps]

Well done SPH76 !

I have accept your solution.

I would have wasted lots of time trying to recreate your problem before it occured to me it was a calculation group, which is why I asked for example.

Good work.

[speedramps]

We want to help you but your description is too vaugue. Please write it again clearly.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.
Also provide the example desired output, with a clear description of the process flow.

Remember not to share private data ... we don't want you to get into trouble. 😧

Take care to use the same table and field names in the input, output and description so we can understand your problem and help you.

You will get a quick response if you put time and effort into writing clear problem descriptions.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming

[SPH76]

It's vague because so is my issue 🙂 sorry. I will try to clarify. There is a table:

ID	Date1	Date2	Count
1	01-01-2022	28-01-2022	3
2	15-01-2022	05-02-2022	5
3	06-02-2022	14-02-2022	4

Then ur common date table with 2 (actually 3 but that does not matter here) inactive relationships on the date, not going to repeat that here.

Then a third table (conspicuously named "TimeIntelligence..."), unrelated to the other two, with:

TimeCalc	Ordinal
A (just a name for one of the inactive relationships)	1
B	2
C	3

The measure is a total of the Count, nothing more. Normally you would use measures with USERELATIONSHIP to choose between the 2, but this is not the case in this PBIX. They put the third table in the FILTER of the visuals and choose which relationship to activate. When u pick relationship A in the FILTER pane it performs a calculation asif USERELATIONSHIP(A, Fact) was used, etc.

It works, and that is my question, why??? It does not make any sense. It is as if the ordinal value is somehow referencing the inactive relationship, but how? 

[speedramps]

Please can you save the PBIX with share access in PneDrive or DropBox and post the link here.

Sorry but I wont spend lots of time creating the PBIX for you but I will take a look at the PBIX if you can recreate the problems with non private data,

Here is an example of how to add a pbox to OneDrive

https://1drv.ms/u/s!AgMTUY3Uvq3bg7IbYncAD9xRkf4Rug?e=KMv7C1

[speedramps]

Try try this

https://1drv.ms/u/s!AgMTUY3Uvq3bg7IbYncAD9xRkf4Rug?e=KMv7C1

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[SPH76]

Thnx but what u describe is what I know, using USERELATIONSHIP. The point is, in the entire file this function is not being used, I checked all the measures, even the ones not used in the visual mentioned. Nothing.