Forum Discussion

ABideImen___28's avatar
ABideImen___28
Frequent Visitor
4 years ago

Multiple Date in the fact table

Hello ,

I have a fact table containing the IDS of 3 date: start date, end date and collection date and dimension Dim_Time I want to make a matrix that displays the 3 dates and I can't do it, I tried this dax formula but it shows me empty columns:

CALCULATE( SELECTDVALUE(Dim_Time[Date] , USERELATIONSHIP( Fact[id_date_Finish] , Dim_Time [id_time])

I hope you can help me please.

 

Need Help
Tips and Tricks

8 Replies

  • ABideImen___28's avatar
    ABideImen___28
    Frequent Visitor
    4 years ago

    hi Shishir22  thanks for replying me

    Here is my first Fact Table that contains id_date_first and id_date_end : 

     

    My second fact containt id_date_encaissement : 

     

    here is the schema both fact tables are related to Dim_Time and only one relationship is active between them ( there is another dimensions that why there is so many relationship in the schema)  :

    And i tried this measure to retrieve the finished date :

    and here is what it shows  ( blank)  ( the first date is showen because the relationship is activet): 

    I hope you help me

    • NickolajJessen's avatar
      NickolajJessen
      Icon for Solution Sage rankSolution Sage
      4 years ago

      The reason you're having problems is because your datamodel is a mess 😞 
      These 1-1 bidirectional filter between fact tables with screw you over big time, you don't get it right.

      Consider wathing some best practive videos on datamodelling. I've used Avi Singh myself.

      In you case you need to have a dimension table, that connects the etat and treso table on the ID-column

       

      • ABideImen___28's avatar
        ABideImen___28
        Frequent Visitor
        4 years ago

        Hi NickolajJessen  thanks for answering me

         

        I remodeled my star model and I rectified the relationship between the fact table and the dimension by choosing a  one-way  and one to many relationship and the dax expression still does't work i hope you can suggest me another solution or dax expression 😞 

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  ABideImen___28 ,

    You might consider creating a custom matrix with custom layouts for the columns of the matrix.

    https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591

    https://community.powerbi.com/t5/Desktop/Matrix-column-header-label-custom-group/td-p/1253196

     

    Best Regards,

    Liu Yang

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