Matrix: Make end users select category they want (2 columns with different categories)

[PeterStuhr]

Hi all,

In my organization we have to report on revenue per service we are offering.

Instead of just showing each service, we want to group those. We have 2 different groupings dependent on the audience of the report.

Example:

Service	Category 1	Category 2
Service 1	Delivery	Service
Service 2	Delivery	Service
Service 3	Delivery	Service
Service 4	Service	Service
Service 5	Service	Service
Service 6	Service	Service
Service 7	Tech	Tech
Service 8	Tech	Tech
Service 9	Tech	Tech
Service 10	Tech	Tech

Instead of having to build 2 different tables using Category1/Service, and Category2/Service (Like shown in the picture), is it possible somehow to combine it, so an enduser can SELECT which combination they want instead? To give more flexibility and less work.

Thanks!

[v-xulin-mstf · Accepted solution]

Hi @PeterStuhr,

Try this in query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tKstMTlUwVNJRcknNySxLLaoEMqHCSrE6CCVGhJUYE1ZigiSOXYUpQRVmBFWYA8VDUpMzYBSynAUeOUs8coYG6JKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Service = _t, #"Category 1" = _t, #"Category 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Service", type text}, {"Category 1", type text}, {"Category 2", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Service"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Category"}, {"Service", "Service"}, {"Value", "Subcategory"}})
in
    #"Renamed Columns"

Create a measure as:

Measure = 
var a=CALCULATE(
    MAX('Table'[Service]),
    FILTER(
        'Table',
        'Table'[Category]=MAX('Table'[Category])&&'Table'[Subcategory]=MAX('Table'[Subcategory])
    ))
    return
    IF(
        a<>BLANK(),
        1,
        0)

 Here is the output:

Here is the demo, please try it.

Best Regards,

Link

[amitchandak]

@PeterStuhr , the expected output is not clear

But they are not mutually exclusive Category 1 and Category 2. So that means data duplication if want both of them in a row.

[PeterStuhr]

Hi @amitchandak 

What I was looking for is if we could do something or change the structure, so you by a filter or something could select to use either "Category 1" or "Category 2" together with the "Service".

Instead of me having to select either Category 1 or 2 when designing the report

[amitchandak]

@PeterStuhr , if one of the two tables from the screenshot. Bookmark is one option that comes to my mind.

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive