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.
Hi @Helal, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster.
When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Hi there,
I thought I did exactly what guidelines recommended. First, I don’t have formula in the title. It’s just text. Will you please let me know how I can decode it to see the formula? second and if I understood guidelines correctly, I provided my problem to solve and then provided templates including desired outcome. This is what’s suggested. Again, please let me know where I didn’t follow guideline.
Keith,
I didn’t generate Power BI report since I had no idea where to begin. The only thing and came to my mind but haven’t tried was HASONEFILTER. All examples I saw with HASONEFILTER includes aggregate calculations. In my case, there are no calculations/aggregations. I can create mock data sets in Power BI if that helps.
Hello @Helal, in order for the users and experts to help you, please provide a PBIX file. You can also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.
Hi Keith,
With some help I came up with this solution (See PBI File link below) but it’s far from ideal. Though, model (merged tables) is working for few tables, but it’s almost impractical when merging 90+ tables together. Let me know if I can provide more clarifications.
Is the data in each of your tables similar?
in your example, you have a Form No, Subject ID, and then Col 1, Col 2, and possibly Col 3
so I realize you are saying that the number of columns is variable, but would Column 1 values belong together if your slicer was looking at two different form numbers?
If so, a solution like this might work for you:
Appending tables (with having the names cleaned up to match on each table)
if that WON’T work, then you may have the best possible scenario already figured out.
Thank you Heather. Data in Table are not similar. For example a Table is Demographics and another Table contains data on let’s say depressions. However, the have common keys to join. The solution of merging all in a Table works except that I have more than 90 tables with close to 2500 variables. So the merge table is going to be huge and perhaps not practical. I am reviewing your other solution and will keep you posted.
