Latest Enterprise DNA Initiatives

Entire table extraction with slicer selection

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 Example

Thank you,
Helal

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 </>.

image

  • 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.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum.

Not adhering to it may sometimes cause delay in getting an answer.

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.

Thank you,
Helal

they are asking for your power bi file too? did you give it a try?

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.

you need to give it a try so people in your the forum know where they can help

2 Likes

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 @Helal, we’ve noticed that no response has been received from you since the 24th of May.

We are waiting for the masked demo pbix file and any other supporting links and details.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

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.

Thank you,
Helal

Hi Keith,
I posted the pbix file last week and yet to receive any responses. Is this thread no longer active? do I need to repost my question?

Helal

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:
image
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.

I have attached your file (with the append solution) below
Entire Table Columns Extraction with Slicer Selection.pbix (58.5 KB)

I have also attached a solution I created a while back on creating a variable table (not QUITE what you are looking for, but it might point you in another direction).
Variable Table (by item, by rep, by account) based on slicer selection.pbix (108.9 KB)

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.

Thank you,
Helal