0

To start off, I am creating a table schema in Power BI with the use of R to wrangle all of my data.

Here is a simple example of my issue. I have a table with ID numbers and several demographics related to each ID. In this case, you'll see 3 demographics related to each ID with a true/false for each demographic (in my work situation, I actually have 95 demographics).

ID Female Veteran Government
1 TRUE FALSE FALSE
2 FALSE FALSE TRUE
3 TRUE TRUE TRUE
4 FALSE FALSE FALSE

So, let this be the fact table. A dimension table would look something like this:

Demographic Key
Female 10
Veteran 11
Government 12

I need to create a relationship between these two tables. I'll be using Power BI, so I can only use one direct relationship. The main purpose is to be able to create visualizations that will filter down on a user's selection. For example, if the user is interested in how many ID's are female and veteran, the graph would only show ID #3.

As is, the dimension table will not work because there are no keys in the fact table to connect the two. For it to work properly, I would need one, and only one, column in the fact table with a key that connects to the dimension table. That would look like this:

ID Female Veteran Government Key
1 TRUE FALSE FALSE 10
2 FALSE FALSE TRUE 12
3 TRUE TRUE TRUE 10, 11, 12
4 FALSE FALSE FALSE

This doesn't work because Power BI won't "search" for a key within the "Key" column. It can only have one key per row, not a set of keys, as far as I'm aware. I could potentially make keys that would be the combination of demographics. So, for ID #3, the key would be "10_11_12" and then have that exact key within the dimension table, too. But, as mentioned above, I have 95 demographic columns and that's a right massive mess.

I have also tried to make the initial fact table above long instead of wide:

ID Demographic Value Key
1 Female True 10
1 Veteran False 11
1 Government False 12
2 Female False 10
2 Veteran False 11
2 Government True 12
3 Female True 10
3 Veteran True 11
3 Government True 12
4 Female False 10
4 Veteran False 11
4 Government False 12

However, Power BI will only aggregate the data. That is, for our example of female and veteran, the graph will show any ID that is female as well as any ID that is veteran. So, the result would show ID's #1 and #3, but it should only show #3 (I need female and veteran not female or veteran).

Any ideas of how to get a dimension table and fact table to work well together for my situation?

abet
  • 175
  • 1
  • 8

2 Answers2

0

If this is your Fact Table

(ID,Female, Veteran, Government)

Your dimensions would be

DimFemale, DimVeteran, DimGovernment, etc. Each Dimension would have two rows and probably only a single column. So you typically just don't use dimension tables when the you don't have any data other than the dimension key.

I am wanting a filter in the filter pane that has a drop-down of all 95 demographics.

That's not a dimension table, that's a child fact table with bi-directional cross filtering. Just have (Id, Demographic) and only add the ones that are TRUE.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Yes, that would probably work in this example, but I have 95 demographic-type columns in my actual data (not the 3 in this example) and that would be a ton of dimension tables. :) – abet Dec 03 '21 at 18:03
  • So just don't create dimension tables. If a dimension has no columns other than its key, then there's no point in creating a separate table. PowerBI doesn't _need_ dimension tables; you can filter and group by fact table columns. – David Browne - Microsoft Dec 03 '21 at 18:21
  • Does the ID refer to a person i.e. Female, Veteran, Government are all attributes of a person? If so, why not create a Person dimension with all these attributes? – NickW Dec 04 '21 at 11:15
  • 1
    Thanks, David. Your edits made the most sense and I added another table that had bi-directional cross-filtering with just TRUE's. I appreciate your help! – abet Dec 06 '21 at 12:30
0

You only need the first table, no need to complicate.

If the user wants to know the number of ID's that are female and veteran, you would have a slicer/filter for female and veteran and the results would be filtered accordingly.

enter image description here

Joao Leal
  • 5,533
  • 1
  • 13
  • 23
  • Thank you for this and I understand what you mean. However, I have 95 demographic-type columns (not just the 3 in this example). So, I am wanting a filter in the filter pane that has a drop-down of all 95 demographics. If I put a slicer for each filter, then there will be 95 slicers. Hence, the reason for wanting the dimension table because that "Demographic" column of the dimension table becomes my 1 filter in the filter pane with all 95 demographics (or 3 in this example), if that make sense. I just need it to be able to cross-reference the original table, which I haven't got yet. – abet Dec 03 '21 at 18:01
  • But if you want to pick 4 items in the demographics you still have to select where the 4 are all true, or do you mean that you only "assign" the values where true? – Joao Leal Dec 04 '21 at 22:41
  • I believe that when you select multiple values in a slicer the behaviour is OR rather than AND, so you'll always have some trouble with it. One solution could be to use your last table and then have slicers 1 to N where you establish the filter by creating a special measure that reads the selection on each slicer and then filters with an AND condition. You'd still be limited to just N slicers. – Joao Leal Dec 04 '21 at 22:45
  • Another option could be to have 95 slicers. Pick the top 10/20 most used, put them in a hidden panel, add a show more option that shows the others with some sort of pagination or whatever. – Joao Leal Dec 04 '21 at 22:46
  • Thank you, Joao. These are all great ideas and I tried them out, but the one that ended up being easiest is David Browne's suggestion below after making edits (the bi-directional cross filtering with a table of only TRUE's). I really appreciate your time and help! – abet Dec 06 '21 at 12:29