Forum Discussion

101Mathew's avatar
101Mathew
Icon for Advocate V rankAdvocate V
4 years ago

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

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.

 

       

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      101Mathew
      Icon for Advocate V rankAdvocate V
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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.