Problem with data model and establishing relationship after unpivoting columns

[Maciek_]

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

[v-yalanwu-msft]

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_]

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

[lbendlin]

If you consider both "Append"  and "Dep Details" to be fact tables then that is fine - just don't try to link them directly. Link them via common dimensions, and then use the dimension fields for the visual filters.

[v-yalanwu-msft]

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.

[Maciek_]

Hi again @v-yalanwu-msft I am afraid that is not at all what I need 😞 the expected output is shown on one of the screenshots above and also here again

In small table I should see the details of the activities based on the selection (filtering) from bigger table - if I select a row in a bigger one, the small one should display attributes taken from Env details table based on selected "Env.ID" in the first column and attributes taken from Lversion details table based on selected "Lversion.ID" in the second column.
@lbendlin I tried using Dep details table as a fact table as well but for both this table and Env details table Lversion.ID can be repeated so relationship between these cannot be other than many to many...

[lbendlin]

You can get away with Many-to-Many relationships as long as you use a single filter direction.

[lbendlin]

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?