cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
eldarkt
Frequent Visitor

Filter by multiple sets of column

Hi all, I have a data set like this:

 

Customer1_value1Customer1_value2Customer1_value15Customer2_value1Customer2_value15CustomerN_value1CustomerN_value15
0.2291516520.1550577210.3245843120.1104796620.0902755020.9676822910.48551334
0.5472410.7675854910.9060742920.4439835870.3953988690.599606490.221637108
0.8956288990.8143163640.9496358270.1019848960.9221496490.0596270220.065165743

 

So this is N sets of 15 columns. 
Technically, N<20, but the main problem for me it's dynamic, N will change from time to time.
One set for every N value always contains 15 columns, this value is static.
All data types for different N values is the same (for example, Customer1_value2 and Customer2_value2 and CustomerN_value2 columns has the same data type)

What I wnat to achieve is to be able to filter all my visual by N (for example, with "slicer" visual).
Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
sdjensen Solution Sage
Solution Sage

Re: Filter by multiple sets of column

Hi @eldarkt,

 

This approach should do the trick

1. select the column 'Attribute.2'

2. Select 'Pivot Column' on the Transform tab

3. In the pop-up select 'Value' as Values Column

/sdjensen

View solution in original post

6 REPLIES 6
Super User I
Super User I

Re: Filter by multiple sets of column

@eldarkt

 

i am just trying to understand what you are asking for.

 

You want to take the below 15 columns and be able to filter all of them at the same time? so combining all of this into one column via a pivot wouldn't work for you or would? 


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
eldarkt
Frequent Visitor

Re: Filter by multiple sets of column

What I want to achieve is convert from this structure:

DatetimeCustomer1_value1Customer1_value2Customer1_value15Customer2_value1Customer2_value2Customer2_value15CustomerN_value1CustomerN_value2CustomerN_value15
datetime10.2291516520.1550577210.3245843120.1104796620.160268910.0902755020.9676822910.4938300840.48551334
datetime20.5472410.7675854910.9060742920.4439835870.2237046440.3953988690.599606490.866450090.221637108
datetime30.8956288990.8143163640.9496358270.1019848960.9775577590.9221496490.0596270220.2341682440.065165743

 

 

to this:


Datetimevalue1value2value15Customer
datetime10.2291516520.1550577210.324584312Customer1
datetime10.1104796620.160268910.090275502Customer2
datetime10.9676822910.4938300840.48551334CustomerN
datetime20.5472410.7675854910.906074292Customer1
datetime20.4439835870.2237046440.395398869Customer2
datetime20.599606490.866450090.221637108CustomerN
datetime30.8956288990.8143163640.949635827Customer1
datetime30.1019848960.9775577590.922149649Customer2
datetime30.0596270220.2341682440.065165743CustomerN

 

Sorry, I am trying to not overcomplicate things...
Thanks!

sdjensen Solution Sage
Solution Sage

Re: Filter by multiple sets of column

I guess you could try something like this:

1. Open the Query Editor

2. Select the Query that loads your table

3. Select all the columns besides your values columns

4. On the Transform tab open the drop down menu on 'Unpivot Colomns' and select 'Unpivot other columns'

5. Select your column with the values Customer1_value1, Customer1_Value2...CustomerN_ValueN and use the Split Column By delimiter (on the Home tab) to split the column into 2 column. Use '_' to split the column

6. Detele the column with the values Value1, Value2...ValueN (if you don't need it)

7. Rename columns to what ever you want.

/sdjensen
eldarkt
Frequent Visitor

Re: Filter by multiple sets of column

Thanks, sdjensen!

Now I have this:

DatetimeAttribute.1Attribute.2Value
datetime1Customer1_value10.229151652
datetime1Customer1_value20.155057721
datetime1
datetime1Customer1_value150.324584312
datetime1Customer2_value10.110479662
datetime1Customer2_value20.16026891
datetime1
datetime1Customer2_value150.090275502
datetime1CustomerN_value10.967682291
datetime1CustomerN_value20.493830084
datetime1
datetime1CustomerN_value150.48551334
datetime2Customer1_value10.547241
datetime2Customer1_value20.767585491
datetime2
datetime2Customer1_value150.906074292
datetime2Customer2_value10.443983587
datetime2Customer2_value20.223704644
datetime2
datetime2Customer2_value150.395398869
datetime2CustomerN_value10.59960649
datetime2CustomerN_value20.86645009
datetime2
datetime2CustomerN_value150.221637108
datetime3Customer1_value10.895628899
datetime3Customer1_value20.814316364
datetime3
datetime3Customer1_value150.949635827
datetime3Customer2_value10.101984896
datetime3Customer2_value20.977557759
datetime3
datetime3Customer2_value150.922149649
datetime3CustomerN_value10.059627022
datetime3CustomerN_value20.234168244
datetime3
datetime3CustomerN_value150.065165743


Do you know is it possible to convert it to structure described (under "to this:") in my previous post?
I tried "pivot", it didn't work...

Thanks again for your help!

sdjensen Solution Sage
Solution Sage

Re: Filter by multiple sets of column

Hi @eldarkt,

 

This approach should do the trick

1. select the column 'Attribute.2'

2. Select 'Pivot Column' on the Transform tab

3. In the pop-up select 'Value' as Values Column

/sdjensen

View solution in original post

eldarkt
Frequent Visitor

Re: Filter by multiple sets of column

 

Many thanks, sdjensen, it works!

My mistake when I tried pivot was I didn't turn off aggregation here (it was "count(all)" by default) :

pivot.jpg
and it showed "1" in each field instead of source values.
I changed it to "Don't Aggregate" and it did the trick.
What a wonderful forum it is! Smiley Happy

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors