Forum Discussion

Maciek_'s avatar
Maciek_
Frequent Visitor
4 years ago

Problem with data model and establishing relationship after unpivoting columns

Hi All,

I’ve been trying to introduce one feature to my existing report but seem to be stuck with creating correct relationships or data model.
My sources are sharepoint lists. I have a table “Dep details” containing a list of activities (with unique ID as “Dep.ID”). Each activity consists of one certain environment (table “Env details”), one certain version (table “Lversion details”) and some additional information.
Tables Env details and Lversion details include similar information about systems used for each environment and version.

The idea is that if I select particular activity in a table in my dashboard I can lookup and compare information about the systems used for environment and version under this activity. I was able to create two separate tables using simple one to many relationships from Env details to Dep details and from Lversion details to Dep details

 

 

 




Now I want to see the same information but in one table (matrix) of two columns so it is more visually friendly. I figured I would need to unpivot columns containing information about the systems. I did so, added custom column of “Type” to have the column headers of “Environment” and “Lversion” displayed in new matrix visual and appended both tables into one.

 

 

 



The problem is that now I am not able to establish correct relationship and therefore the data displayed in my matrix is of course incorrect.

Do you have any suggestions on how I can solve the issue?

Regards,

Maciej

Need Help

7 Replies

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

    Ask yourself which of your data points you want to compute (the "facts") and which of them you want to use as filters (the "dimensions").  The data model should be  star schema, with the dimensions controlling the facts in a 1:* relationship and single direction.  Is that something you can implement?

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

    Hi， Maciek_ ；

    Could you please consdier sharing a simple file after removing sensitive information and posting expected result so it is clear ? 
    It makes it easier to give you a solution.

     

    Best Regards,
    Community Support Team_ Yalan Wu

    • Maciek_'s avatar
      Maciek_
      Frequent Visitor
      4 years ago

      Hi v-yalanwu-msft ,
      Sure, Please check the file here 
      lbendlin, I am aware of star schema but I cannot see a possibility of establishing correct relationship between "Append" and "Dep details" tables after I unpivot data in "Append" table (I do this so I could represent data in a matrix consisting of two columns). Different activities in Dep details can include the same Environments and Lversions which means there is no unique column I can use for 1:* relationship. Of course *:* relationship between these two tables return incorrect data in my visual - sometimes one column is missing, sometimes both and sometimes the values for different env.ID or Lversion.ID are displayed.



      Best regards,

      Maciej



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

        Hi, Maciek_ ;

        You could create  a measure as follows:

        Measure = CALCULATE(MAX([Value]),FILTER(ALL('Append and unpivot - Lversion + Env'),[Type]=MAX([Type])&&[Value]=MAX([Value])))


        The final output is shown below:

        If not ,can you share me the result what you want to output?


        Best Regards,
        Community Support Team_ Yalan Wu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.