Look up table "master value" possible?

[Anonymous]

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?

[amitchandak]

@Anonymous , Try Unpivot

https://radacad.com/pivot-and-unpivot-with-power-bi

Or Transpose: https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[Anonymous]

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-msft · Accepted solution]

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

[FrankAT]

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

[Anonymous]

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