cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Highlighted
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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

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.

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: 207 members 2,040 guests
Please welcome our newest community members: