How to create a specific type of relationship

[emma313823]

Hi All

I'm extremely new to power BI and hope that someone can offer some simplistic instructions on how to accomplish something in a table I've imported using Excel.

I will try to explain the table as best as possible.  I have commission checks that come in from multiple principals/customers.  Each check gets entered into one row on the spreadsheet.  The header row has 5 territory columns - Metro, Mid, NE, PA and Upstate NY.  The check value can drop to only one of these or it can drop to multiple territories.

I've created multiple visuals and have been successful in creating slicers for all categories I need so all the charts change accordingly, however I'm stumbling on how to get the charts to change according to the territory.  When I grab a slicer and drop the Metro column it just displays the values for the various checks that have some portion attributed to it.

I want to create a slicer that will show Metro, Mid, NE, PA and Upstate NY as the options.  I don't have the option of modifying this flat file, so is there some way to do this?

I can send a sample file if someone can assist in helping me to figure this out.

Emma

[v-micsh-msft]

Hi emma313823,

To have the Metro, Mid, NE, PA and Upstate NY shown as the options in Slicer, we need those to be column value in a single column.

What smoupre suggested should be an available way to have those column name transferred into the values of the “Territory" column.

However, if you just select those five columns in the fact table, and choose unpivot, there might be duplicate entries created, we might need to think out another way to deal with that.

You may take a look at the thread below for reference, regarding the unpivot column.

http://community.powerbi.com/t5/Desktop/Unpivot-Group-of-data/m-p/62142#M25577

If any further assistance needed, please feel free to post back.

Regards

[emma313823]

Thanks for the help.  I'm curious - if from this file I extrapoloated the one unique value from my original file - the check number and moved the check number, date, check values for each territory and of course the territory to drop vertically...so the territory is in a column struture, then pulled both files via get data (excel) into power BI and created a relationship based on the check number - would this work to get what I want?  Is Power BI intuitive enough to allow me to still create my previous visuals but from 2 source files instead of one? Or am I now moving into the realm of having to figure out how to write a query against these?

Emma

[Greg_Deckler]

You are right on track here @emma313823! That's exactly what you would want to do in this case. So, unpivot your data and then only keep like the check no in that data table. In another query from the same exact file, pull in all of your other fields along with check no. Then create a relationship between the two tables based on check no and then you *should* be able to easily create all of your visuals.

[emma313823]

Forgive me for my ignorance as I navigate this project.  Still a bit confused on your last direction...are you saying to unpivot my data and keep only the check number?  Should I keep the check number and each of the territories - metro, mid, ne, pa and upstate ny with their values?  I'm not sure I understand how using unpivot and grabbing just the check number is going to allow me to get the territories in a column structure so I can get the ability to use a slicer using territory.

So assuming I figure out the above - going with the unpivot option - once I have what I want unpivoted and a relationship established and my visuals created  - how will I be able to use onedrive or a personal gateway to always keep the dashboard updated if I modified the original worksheet by unpivoting specific columns?  Is this software intuitive enough to know that new data has been added and that it should unpivot automatically to keep the dashboard/visuals updated?

Thanks

Emma

[Greg_Deckler · Accepted solution]

Well, you can certainly keep as many columns in your first table as you want but I don't think you would need to. The by unpivoting the rows, you should end up with something like:

CheckNo,Category,Value

00001,Metro,1170

00001,NE,42

00001,PA,1

etc.

With all of your other fields in your other table, you could display all of the data about the check from there and all of the category information would come from your second table (with the unpivot).

Yes, since the unpivot is part of the query, it should run that way everytime as it loads data into the data model.

[Greg_Deckler]

The good news is that you can easily handle this. The bad news is that you are probably going to have to change everything else.

In the query editor, select those 5 categories and then select "Unpivot Columns" from the Transform tab of the ribbon. Rename "Attribute" to "Territory".