Forum Discussion
Look up table "master value" possible?
Hello all,
PBI Noob back again.
I have a report that draws on 3 different IT systems data to produce.
I have created a table that is like the below....
| Master Value | System 1 | System 2 | System 3 |
| Slip Trip Fall | Fall from Height | Slips, Trips and Falls | |
| Mobile plant | Powered Mobile Plant | Vehicle Incident | |
| Electricity | Electricity | Electricity | Electrocution |
| Confined Space | Confined Space |
If possible I would like a way to rationalise System 1,2,3 terminology into the "master value" so that when I am applying slicers or measures etc... I can do something to the effect of...
Count Slip Trip Fall = COUNT(tablename[Slip Trip Fall])
Then I would want this measure to look for all 3 systems terminology and count them.
Also if I was to include a slicer based on the "master value" I would only want it to display the "master values" but filter all 3 systems into the visualisations.
Is that possible?
Hi Anonymous ,
You can unpivot the last three columns and filter out the blank value in value column :
>>The slicers in the last image, My aim is to have only the 1st? is that not possible?
Would you please explain it more clearly？ If you don't want slicer, You can add a page -level or visual-level filter for it.
Best Regards,
Dedmon Dai
5 Replies
- FrankAT
Community Champion
Hi Anonymous ,
unpivot your three system columns with Power Query like this:
// Table let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hU5BDgIhDPxKw3l/YTR6MNlkjRfCAUvXbYKFAMb4ewE5efEwbWY601ZrtXiOcEm1HKz3alKtwZrCA47E961UCSqaL0/dmcGK6/YMykxancONPUH0Vpp9Di9K5GDI85CvtDFWfhJkR/Jd3OJ7T1gSI5d31f6zgM/CQXp2F2RlqceWaJHGrw0/A2M+", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Master Value" = _t, #"System 1" = _t, #"System 2" = _t, #"System 3" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Master Value", type text}, {"System 1", type text}, {"System 2", type text}, {"System 3", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Master Value"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "System"}}) in #"Renamed Columns"Use it in your report like this:
Regards FrankAT
Thanks,
that 1st image looks like my nightmare! I wouldnt even begin to understand how to use that!
The slicers in the last image, My aim is to have only the 1st? is that not possible? My issue is... I understand how things interact and how to get the result I am after. I am eventually going to need to make this into a dashboard and give access to around 100 people or so to "self manage" their reporting needs... these users will not understand... it needs to be hammer in the face obvious as to what they need to do...
- amitchandak
Super User
Anonymous , Try Unpivot
https://radacad.com/pivot-and-unpivot-with-power-bi
Or Transpose: https://yodalearning.com/tutorials/power-query-helps-transposing-data/
Hey amitchandak
Thanks for that.
How does changing the layout solve my problem? Would I then create a 2nd table for the unique values in the "master values" and create a 1 to * relationship to the un-pivoted data?
v-deddai1-msftCommunity Support
Hi Anonymous ,
You can unpivot the last three columns and filter out the blank value in value column :
>>The slicers in the last image, My aim is to have only the 1st? is that not possible?
Would you please explain it more clearly？ If you don't want slicer, You can add a page -level or visual-level filter for it.
Best Regards,
Dedmon Dai
