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.
If so, you can obtain it without changing any of your measures or relationships, just by switching the date on the x-axis from the calendar table from hierarchy to date.
Thanks for taking a look - I dont think this works because it still doesnt represent the issue created in October: 4/10/2022… … If you look it still is using the enquiry date received values
In the measure, I thought it cleared any filter on the date table, but for some reason, it still has the filter on the month of September…
OK, I now better understand what it is you need to do here. Can you please attach the xlsx file, since i need to clear up some type mismatches in PQ for the solution I want to try. Thanks!
In working through this, I think the easiest and best way to do this is to create a disconnected version of the calendar table, use that in your slicer, harvest the values from it and then use those values to set the filter condition in your measure. That way you don’t have to worry about removing the context imposed by the slicer, since given that it’s disconnected it doesn’t change the filter context at all.
Much appreciated. Is this where you create a reference to you existing calendar table? I can do that, just wondering how I get the relationship to the dates?
Used MonthDateSort field to link disconnected slicer values to filter condition via DAX.
Active Issues Rcvd Months =
VAR SelRcvd =
VALUES( 'Disconn Cal'[MonthofYearSort] )
VAR Result =
CALCULATE(
[Active Issues],
t_Fact[MonthofYearSort] IN SelRcvd
)
RETURN Result
Full solution file attached below. Hope this is helpful.
- Brian
[EDNA Forum - DebugPBITest BEJ Solution. pbix.pbix|attachment](upload://5nQboKOYGBjMmD0ZL1FHPUawSDq.pbix) (165.3 KB)
