Entire Table Columns Extraction with Slicer Selection

[helalm]

I have multiple tables that are joined by key column (SubjectID) in an Star schema. The Main Table is left joined with the rest. Each table has a common column (Form No) that contains unique identifier for the Table. What I am trying to design is to create a slicer based on Form No and once one or more Form No values are selected, the entire Table columns associated with those Form No are populated in a Matrix. Rows are matched already with Main Table and columns of each Table are added after Main Table columns. Attached is an example of three Tables (Main Table, 1, and 2). Each Table has first column as Form No with unique values. Each Table has different number of rows and columns. The Desired Outcome Matrix includes fixed Main Table rows columns and followed by, in this example, all columns from Table 1 & 2 for corresponding SubjectIDs. This is assuming that Table1 & 2 (according to their Form No values)were selected in the slicer. In another scenario, Table or Table 2 may be selected only. I hope this make sense. Any guidance in designing the data model or slicer is greatly appreciated.

Data Streaming wiht Power BI 

Thank you,

Helal

[MFelix]

Hi @helalm ,

Looking at you model and taking into account that you have several columns that may vary in number of columns have you tried making the unpivot of your tables and then make a append of the data?

That way you would have a single table with:

Form

subject ID

Attribute (column 1 to X)

Value

Then just use that table for your visualization using the attribute column has columns values.

For simplifing the treatement of the tables you have you can create a custom function on the query editor that makes the replication of the unpivot and merging alone and you don't have to upload all the tables in the model and merge them manually.

If you need help with that please tell me.

I used @Anonymous  example to redo the file has refered.

[helalm]

Hi Miguel,

I never thought of that since I am not that savy with PBI. I will give it a try. I may need your help on the second part "creating custom funciton".

Thank you again,

Helal

[helalm]

Hi Rerna,

Is there a way to get help from others? I am failry new to this community but would like to know if others have alternative solutions. If yes, please let me know how.

Thank you,

Helal

[helalm]

Hi Rena,

That's a very cleaver approach. The only drawback is I have close to 100 tables and each table has an average of 50 columns. Merging all table into one flat file will constitue 5000 columns. I don't think that would be very effiecinet method. I should have been clear when I posted the issue. Someone suggested that I shouod look into HASONEFILTER and All, and SELECTALL in DAX. What do you think?

[Anonymous]

Hi @helalm ,

Sorry that I didn’t find the other suitable way to achieve your needs. Maybe you can take a look at the following thread, hope it can help you.

Common Slicers for Multiple Tables

Best Regards

[Anonymous]

Hi @helalm ,

I created a sample pbix file(see attachment) for you by using merge queries in Query Editor, please check whether that is what you want.

If the above one is not your expected result, please provide more details for it. Thank you.

Best Regards