Show multiple dates in a table, via a date table model

[101Mathew]

Hi Power BI commmunity,

What seems like a silly question maybe, but I must confess to needing some advise.

I have multipule dates in my fact table, these are linked via a date table.

I don't like displaying columns from a fact table, I always try to use dimension tables

I use "USERELATIONSHIP" - to change measure to the different dating via the date table.  But if you need to show multipule dates on a report table, I'm a little at a loss how to do this.

[amitchandak]

@101Mathew , Use USERELATIONSHIP, when the same period needs to filter for all dates.

But if they need different periods then use those dates or use multiple date dimension tables in slicer.

Means if sales is joined with date - Active, Then use date slicer for it and for another one use  date from fact as slicer , say order date. Or create one more date dim for that

[101Mathew]

Hi @amitchandak ,

Thank you for replying - Agreed userelationship is the main element to use, but I'm displaying a table of transactions - This table needs to show multipule dates fields for the same transaction.

Using the dates directly from the fact table is not ideal but useable, as is creating another date table.  I just feel there must be a cleaner solution, then using the fact table field directly or creating another date table

Thank you so much for your time.

Regards,

Mathew

[Anonymous]

Hi @101Mathew ,

According to your statement, I think you want to get the result based on the filter of multiple date columns in Fact table. I think you can set all relationships between Fact table and Date table to inactive. Then create measures to achieve your goal.

Fact Table:

Relationship:

Measure:

Measure = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date1] = MAX('Date'[Date])&&'Table'[Date2]=MAX('Date'[Date])&&'Table'[Date3] = MAX('Date'[Date])))

Here I want to calculate result based on the filter of [Date1/2/3]. Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[101Mathew]

Hi @Anonymous ,

Firstly, thank you for taking the time to reply - No this does not, that works if you wish to change dates or show different summarisations of a metric via multipule dates fields

The requirement:
Show all transactions in a table (depending on user selection on a drill-through), show all dates for each transactions (on the same row).

I could just use the facts directly, but I perfer not to - I would perfer to use the calendar table, we could create multipule calendar tables (but feels like over kill)

Regards,