Forum Discussion

nataliesmiy1357's avatar
nataliesmiy1357
Icon for Helper IV rankHelper IV
4 years ago
Solved

Creating Measures without common relationship

Hello!  I have a lot of tables in my report, and they are all connected in some way or another.  But, when I bring in a completion date, it won't show correcly and tells me my relationships are wrong...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, nataliesmiy1357 

    If you want to join the tables, you need to adjust the table structure of table b in Power Query Editor. As mentioned by Ashish_Mathur ,you need unpivot table columns  and add a new column [level] in table a.

    After establishing the relationship between the tables, you can directly drag the fields to the table visual to get the results you want.

     

    Pleasae check my sample file.

     

    Best Regards,
    Community Support Team _ Eason

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

Hi,

TableA should be "Unpivoted" to transform it into a 2 column dataset - Level and Date.  Thereafter, one can bring over the Date in TableB in either the Query Editor (via Merging) or DAX (via a calculated column formula).

Hope this helps.

nataliesmiy1357's avatar
nataliesmiy1357
Icon for Helper IV rankHelper IV
4 years ago

I'm not exactly sure what you mean by this... how do I do that?  And how do I create a dax formula to bring them together?

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

    Hi,

    Share TableA and TableB in a format that can be pasted in an MS Excel file.

    • nataliesmiy1357's avatar
      nataliesmiy1357
      Icon for Helper IV rankHelper IV
      4 years ago

      Ashish - due to confidentiallity, I am sadly unable to share the tables.... how would you do it in Layman's terms?

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, nataliesmiy1357 

        Can you provide more details? In your screenshot, I can see that the current level of the course name is 4. Then how would you confirm the Date of the level 4 you want? Or do you want to join the table like the following to merge related records?

        Best Regards,
        Community Support Team _ Eason