Multivalued dimensions in Power BI

[manmad]

Hey,

I tried searching but I could not find a similar question.

Currently I am working on a dashboard and I am running in a lot of issues with multivalued dimensions (At least i believe that that is my problem, I am no expert at all).

I have 2 fact tables:

• FactRegistrations: Every time a patient or an employee registers anything (weight, blood presure, pulse, ... of the patient for example), we create a new fact. The grain of this table is parameter. So each registered parameter is a new fact.
• FactCarePlanEvent: Every time something happens to the careplan of the patient, a fact is created. So when the care plan is created, started, ended, ....) The grain of this table is a care plan event of a patient.

The first time I encountered an issue with multivalued dimensions was a few weeks ago. Whenever a registration is made, a team of care team members is involved. This registration can never be assigned to one person, so one fact is linked to multiple team members. Power BI cannot handle this and keeps complaining about finding no valid relationship between facts and team members.

I solved this issue by normalizing the tables and creating a bridge table which contains the ID's of all observed combinations of FactRegistration/CareTeamMember. I am not a big fan of this solution as it doubled the size of my PowerBI file. But i could not find a better solution and it works.

But now i have encountered another issue with multivalued dimensions and I would like to know the best way to fix this.

As you can see in the image above, one FactCarePlanEvent is linked to multiple CarePlanAnamnesis, which in turn is linked to multiple CodeSetTypeCodeSetItem. This creates a many-to-many relation (with a table in between). Whenever i encounter 3 tables with cardionalities like this, i can no longer use it in a report. Power BI cannot determine a link between CarePlanAnamnesis and FactCarePlanEvent for example. This is how it is linked in the datawarehouse, so now I have to find a way to transform the data.

I tried searching for a solution but could not find one i could use. I can try to denormalize the CarePlanAnamnesis, but then the issue still exists between FactCarePlanEvent and CarePlanAnamnesis.

Here are some of the more useful links i found incase they may help:

• http://www.sfdama.org/Presentations/2015/DAMA%20Day%202015/Many%20to%20Many.pdf
• http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimens...
• http://stackoverflow.com/questions/21244660/data-warehouse-design-dimension-row-with-multiple-attrib...
• https://dwbi1.wordpress.com/2011/03/13/dimensions-with-multi-valued-attributes/

Has anyone else encountered this issue before and knows the best way to fix this? The only solution i currently can think of is denormalize CarePlanAnamnesis, and then turn the group table in a bridge table with all possible combination of CarePlanAnamnesis id's and FactCarePlanEvent id's. (Like I did the first time with group members). But I am not sure if it will work in this scenario, and it will make my Power BI file even bigger)

Thanks in advance,

Simon

[v-qiuyu-msft]

---

As you can see in the image above, one FactCarePlanEvent is linked to multiple CarePlanAnamnesis, which in turn is linked to multiple CodeSetTypeCodeSetItem. This creates a many-to-many relation (with a table in between). Whenever i encounter 3 tables with cardionalities like this, i can no longer use it in a report. Power BI cannot determine a link between CarePlanAnamnesis and FactCarePlanEvent for example. This is how it is linked in the datawarehouse, so now I have to find a way to transform the data.

---

Hi @manmad,

Can you share some sample data about FactCarePlanEvent,DimCarePlanAnamnesis, CarePlanAnamnesis, DimAnamnesisParameter, CodeSetTypeCodeSetItem tables? As you mentioned "Whenever i encounter 3 tables with cardionalities like this, i can no longer use it in a report. ", would you please elaborate how you want to create a report?

Best Regards,
Qiuyun Yu

[manmad]

Hey @v-qiuyu-msft,

Happy New Year!

I figured you were away during the holiday period so I didn't want to bother you. But I wonder if you already found the time to look at my problem? I hope my explanation was clear enough, I also provided some demo data in my reply as you requested.

I don't want to be intrusive, but I just want to make sure that you haven't forgotten me

Best Regards and thanks in advance,

Simon

[manmad]

Hey @v-qiuyu-msft,

Thank you for taking an interest in my question.

In the link below, you can find a sample .pbix file. I removed the 2nd fact table and all its dimensions to simplify the model for you.

I also made a few visualisations in the report to show you what I would like to do.

https://aenv-my.sharepoint.com/personal/simon_martens_ae_be/_layouts/15/guestaccess.aspx?docid=0d00c...

---

 As you mentioned "Whenever i encounter 3 tables with cardionalities like this, i can no longer use it in a report. ", would you please elaborate how you want to create a report?

Problem

I added an example in my sample file.

This problem occurs whenever the tables are linked like the example above. If i try to make a table or matrix in my dashboard, which contains an attribute of my fact table, and one of the dimension (in this example, FactRegistration and DimCareTeamMember).

I just noticed that this does work if I add an attribute of the fact table and a number of the dimension table. But doesnt if the attribute of the dimension table is a string or something else.

Currently I fixed it in this example by using the solution below, I merged the tables behind the DimCareTeamMember table and added a bridge table which contains the id's of both the dimention and fact table.
So when the tables are linked in this way,  there is no problem creating the visualisations I want.

Solution

I hope I was clear enough. If i was not, don't hesitate to ask more questions or input from me.

Thanks a lot,

Simon

[v-qiuyu-msft · Accepted solution]

Hi @manmad,

As the relationship between two tables in a table visual is N:N, it's not able to display all detail records. You can set the StringValue use Count aggregate function. Or create a measure like below. Please check attached .pbix.

String = CONCATENATEX(FILTER(CarePlanAnamnesis,'CarePlanAnamnesis'[StringValue]<>BLANK()),[StringValue]&",")

Best Regards,
Qiuyun Yu

[manmad]

*Edit*: I looked at my problem again and found that the issue was not with the measure, but with the values I choose for my table. Instead of using the ID from DimPatient, I accidentally used the patient ID from my fact table.  

Now it seems to be working!! Thanks a lot!

Original reply:

Hi @v-qiuyu-msft,

Thank you for your fast response! Your answer is already a big step in the right direction, but it still has one issue.

I tried solving it today but I don't have much experience with DAX and haven't found a solution yet. As you can see in the screenshots of the data below below, PatientSK = 1 is only linked with "Nierfalen".

But if you look on the report, it shows all possible deseases.

You're answer should work if I search on the internet but I have been trying unsuccesfully.

http://www.powerpivot-info.com/post/1499-concatenatex---dax-function-in-excel-2016

This gives slightly different results compared to your code but it keeps giving every combination possible.

My code:

NameUpdate2 = CONCATENATEX( VALUES(SufferedDisease[Name]), [Name], "," ) 

Your code: 

NameUpdate = CONCATENATEX(values(SufferedDisease),SufferedDisease[Name],",")