cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PeterStuhr
Helper V
Helper V

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

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:

 

ServiceCategory 1Category 2
Service 1DeliveryService
Service 2DeliveryService
Service 3DeliveryService
Service 4ServiceService
Service 5ServiceService
Service 6ServiceService
Service 7TechTech
Service 8TechTech
Service 9TechTech
Service 10TechTech

 

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!

 

Capture.JPG

1 ACCEPTED SOLUTION
v-xulin-mstf
Community Support
Community Support

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:

v-xulin-mstf_0-1615184347181.png

Here is the demo, please try it.

 

Best Regards,

Link

 

View solution in original post

4 REPLIES 4
v-xulin-mstf
Community Support
Community Support

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:

v-xulin-mstf_0-1615184347181.png

Here is the demo, please try it.

 

Best Regards,

Link

 

View solution in original post

amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.