Aussie_Dragon Tracker

Re: Removing Context Filters from Calendar and Re-applying New Ones

Aussie_Dragon — Mon, 16 Jan 2023 23:36:50 GMT

Thanks for the reply, just a qucik question, when you say indepenedent date table, can you be more specific? Sorry, I'm not at your level 🙂

Also, would it cater for child records that are created in Dec?

Removing Context Filters from Calendar and Re-applying New Ones

Aussie_Dragon — Mon, 16 Jan 2023 05:22:34 GMT

Hi Everyone,

I have a single entity, denormalised into that a single table containing Enquiries and Issues.
The denormalised entities would be a 1 to M (Enquiries as the one; Issues as the many)

The Goal
When you select a month that an active enquiry was received (date received), display all the related issues and display them on a timeline when they were created.

The Issue
When you select the particular enquiry date received month, it applies a filter to the model only for that month. However, some issues may have their i.createdon date in the following month, so PowerBi only shows the issues in the same enquiry month.

Progress so far
I have tried to remove the filter off the calendar table when writing the calculation and get the correct count, however, the timeline still only displays the initial filtered enquiry month.

i.e I click the timeline for September but there is one issue created in Oct (Oct doesnt appear on the timeline).

If I use the i.createdon date from the fact table, it works, but I know that is not best practice. Would appreciate any help to understand if what I am doing is the best way to do this and if it is possible.

Many thanks,

Powerbi file: Powerbi File

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

Aussie_Dragon — Mon, 05 Dec 2022 05:11:54 GMT

Thank you.

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

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

Aussie_Dragon — Sun, 04 Dec 2022 22:34:32 GMT

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 🙂

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

Aussie_Dragon — Sun, 04 Dec 2022 08:53:31 GMT

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.

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

Aussie_Dragon — Sat, 03 Dec 2022 03:44:01 GMT

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