Forum Discussion

HabibAdil's avatar
HabibAdil
Helper IV
6 years ago
Solved

Creating Matrix with Data from Unrelated Tables

Hi,

Can someone please tell me how can I replicate this matrix in power bi (image 1). The Quote and Sales data are in different tables with different dates (quote received date, sales order date). I have extracted months and years from these dates.  However, I ended up with Month and Year Columns in each table. If I put month from one table in the Row option in the matrix, it does not work for another table as they do not have relationship. For example in below SO Header table is not connected/related to Quotes table. If I try to put the two dates (Month) columns from the two tables into the Row option , it creates hierarchy which I don't want.

I created my own calendar table (Month ad Year columns )and then connected these columns with Month and Year in the other tables. I used the calendar table to filter the year and I used the Month column in the Matrix Row option. It did not work.

 

Thanks, Habib

 

 

 

 

image 3

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi HabibAdil ,

     

    Not sure if i get your point, but you can use SELECTEDVALUE() function if there's no relationship between two tables.

    Example:

    calculate(sum(column1),filter(table,table[date]=selectedvalue(calendar[date])))

    https://docs.microsoft.com/en-us/dax/selectedvalue-function 

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

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

3 Replies

    • HabibAdil's avatar
      HabibAdil
      Helper IV
      6 years ago

      Hi Anonymous 

       

      Thanks for your help. Unfortunatley, this topic is not what I want. I might have to create a matrix for each initiative in excel sheet and put them on top of each other.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi HabibAdil ,

         

        Not sure if i get your point, but you can use SELECTEDVALUE() function if there's no relationship between two tables.

        Example:

        calculate(sum(column1),filter(table,table[date]=selectedvalue(calendar[date])))

        https://docs.microsoft.com/en-us/dax/selectedvalue-function 

         

        Best Regards,

        Jay

         

        Community Support Team _ Jay Wang

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