Represent Different One to Many [Received Dates] on Same Timeline

[Aussie_Dragon]

Hello Everyone,

Apologies for the simple question, I am new to Dax.

Most of the learnings seem to cover Order examples, however, my question relates to scenarios where you have an enquiry which could have multiple issues associated with it.

Order examples generally have the same [date received] on the order line items, but on my learning example, the Issue [date received] may be different from the original Enquiry [Date Received].

Similarly, the Issue [Resolved Date] may be different within the Issue table.

The Problem:
My Date Calendar links to the Enquiry table on [Date Received], and the Enquiry links to Issue on Enquiry No.

However, the Issue visual uses the Enquiry [ Date Received ] rather than the Issue[ Date Received] (if the issue is still open) or Issue [Date Resolved] (if the issue has been resolved)

I looked at UseRealtionship, however, could not get it to work (I think because of the existing one to many relationship)

I think this is a really good example for learning and understanding DAX since it deals with time intelligence and a different scenario other than order details.

Better quality video here if the youtube one is not that great 

[lbendlin]

I looked at UseRealtionship, however, could not get it to work (I think because of the existing one to many relationship)

USERELATIONSHIP is the right approach. Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Aussie_Dragon]

Hi lbendlin,

Thank you very much for the clear instructions.

The raw excel data file and powerbi file can be accessed here.

After doing further research, Home - SQLBI seem to recommend denormalising the data and using USERELATIONSHIP, which I think I have got working, although this will mean whenever counting the enquiries, you need to use distinct . With that in mind, are there any long term implications of models this way, i.e performance or scaleability and more importantly, is my thinking and solution correct for the denormalised model?

This attempt is also in the powerbi file.

Much appreicated.

[lbendlin · Accepted solution]

Here's a preliminary view of how the data model could be structured

Note that the relationship between Enquiry and Issues is inactive (and practically useless) since it would introduce ambiguity. It will be easier to use TREATAS in this scenario.

The main question is - what is your focus on?  Is it a calendar view  ("what happened this month?") or a entity view ("What happened to Enquiries over time?")

[Aussie_Dragon]

I kept researching and  managed to get it working, both in the 1 to many or denormalised form. (those files uploaded and replaced the previous powerbi files).

Really appreciate your redirect questions, they are making me think and explore.

The goal would be to see what happens each month. I am using the one to many relationship to allow the user to click an enquiry record and then filter the issue records to provide a related snapshot. So in terms of being useless, if I understand, I would agree, given that the measures would apply USERRELATIONSHIP - is my understanding correct?

So, if the focus is to represent what happens each month, would it better to denormalise into one table?

TREATAS - wow! just had a quick look at that 🙂

[lbendlin]

There is another option - you can change the search direction to make the relationship bidirectional between one of the fact tables (Enquiry) and the dimension table (Dates).  However that is frowned upon as it will likely create trouble down the road.

[Aussie_Dragon]

Thank you.

Is there any particular book that you can recommend regarding data model design in general?

[lbendlin]

You can start here Understand star schema and the importance for Power BI - Power BI | Microsoft Learn