cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
siljamardla Regular Visitor
Regular Visitor

Displaying a subset of data based on properties in columns

I have a list of items that have some properties, 12 properties to be more precise. As a user I would like to be able to select a property and view the subset of item that have that property. 

Here is some sample data: 
pilt1.PNG

An ugly solution is to have a slicer for each property and a table that reacts to the slicer. 

pilt2.PNG

 

 

 

 

 

 

 

 

 

 

A much more user friendly picture would include a place to select "Items with propery1", "Items with property2" etc.

I can imagine a solution with bookmarks, but it would be a huge effort to build. There would have to be a button for each property, the bookmark would display a table with the specific slicer applied and hide all other tables.


Any better ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
siljamardla Regular Visitor
Regular Visitor

Re: Displaying a subset of data based on properties in columns

@parry2k, thank you, unpivot is indeed the key. I can't do it in PowerQuery because the properties are in fact calculated columns, but using the DAX solution for unpivoting, I could make it work.

Representing the data slightly differently:
pilt4.PNG

 

 

 

 

 

I can generate a new table by:

 

Slicer Table = FILTER(
    UNION(
        SELECTCOLUMNS(ItemTableNew;"Items";ItemTableNew[Items];"Properties";ItemTableNew[Property1]);
        SELECTCOLUMNS(ItemTableNew;"Items";ItemTableNew[Items];"Properties";ItemTableNew[Property2]);
        SELECTCOLUMNS(ItemTableNew;"Items";ItemTableNew[Items];"Properties";ItemTableNew[Property3])
    );
    [Properties]<>"")

which will be the unpivoted list like this:

pilt5.PNG

 

Now I can use a slicer on this list:

pilt6.PNG

 

 

 

 

 

which is what I was hoping to achieve. Thanks!

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Displaying a subset of data based on properties in columns

@siljamardla you should unpivot your table in Power Query and that will do it






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Community Support Team
Community Support Team

Re: Displaying a subset of data based on properties in columns

Hi @siljamardla ,

>>A much more user friendly picture would include a place to select "Items with propery1", "Items with property2" etc.

Could you please share more information about your visualize? It would be better if you can share the target image.

Best Regards,

Teige

siljamardla Regular Visitor
Regular Visitor

Re: Displaying a subset of data based on properties in columns

I imagined the target visualisation like this:


pilt3.PNG

 

 

 

 

 

siljamardla Regular Visitor
Regular Visitor

Re: Displaying a subset of data based on properties in columns

@parry2k, thank you, unpivot is indeed the key. I can't do it in PowerQuery because the properties are in fact calculated columns, but using the DAX solution for unpivoting, I could make it work.

Representing the data slightly differently:
pilt4.PNG

 

 

 

 

 

I can generate a new table by:

 

Slicer Table = FILTER(
    UNION(
        SELECTCOLUMNS(ItemTableNew;"Items";ItemTableNew[Items];"Properties";ItemTableNew[Property1]);
        SELECTCOLUMNS(ItemTableNew;"Items";ItemTableNew[Items];"Properties";ItemTableNew[Property2]);
        SELECTCOLUMNS(ItemTableNew;"Items";ItemTableNew[Items];"Properties";ItemTableNew[Property3])
    );
    [Properties]<>"")

which will be the unpivoted list like this:

pilt5.PNG

 

Now I can use a slicer on this list:

pilt6.PNG

 

 

 

 

 

which is what I was hoping to achieve. Thanks!

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 50 members 1,310 guests
Please welcome our newest community members: