Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ShellyPC
Frequent Visitor

One Slicer - multiple columns

Hi

 

I have data that is structured something like this:

 

Record Id

Person

Cat1

Cat2

Cat3

...

Cat25

1

J Blog

1

0

0

 

0

2

M Blog

1

0

0

 

1

3

B White

0

1

1

 

0

4

D Green

1

0

1

 

0

5

B Good

1

0

0

 

1

6

J Smith

1

1

1

 

0

 

There are 25 columns that contain 0 or 1 (for true/false).   Here I called them Cat1 to Cat25 but in my real data they have names that are meaningful to my users.  (I created these columns with a series of complex case statements in SQL that are looking for specific Anomalies in the records.  This is so that our admin people can know which records they need to fix.)  Each of these columns is not related to any other column so hierarchical filtering is not an option.

 

I would like to have a single slicer that is simply a list of these Cat columns and the user should be able to select multiple of these in the slicer.  Then I want the data to be filtered to only show those records that have a 1 in any of the selected columns.

 

So for instance, if a user selects Cat2 and Cat25, we would like to see records 2, 3, 5 and 6

 

I thought of creating a separate table that is simply a list of the Catn columns and using that as a slicer, but then I cant seem to wrap my head around how to use that to apply my filter.  I can create a Dax measure that returns the selected column names but then don't see a way to use it.  

 

Any help would be greatly appreciated. 

 

Surely this cant be as difficult as I am finding it to be?  I can easily go back to my SQL query and format my data differently if that helps.  Also I am more comfortable working in Dax than in Power Query.

 

Thanks

1 ACCEPTED SOLUTION
bolfri
Super User
Super User

This solution is then you're expecting more KPI columns in the  future.

 

Step 1. Select Record ID and Person (and any other columns that be added and it's strict to that record)

Step 2. Unpivot other columns

bolfri_0-1671638779375.png

 

Use Attribute as a filter.

bolfri_1-1671639290963.png

And set up a filter where Value is not 0

bolfri_2-1671639313247.png

 





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

Proud to be a Super User!




View solution in original post

6 REPLIES 6
ShellyPC
Frequent Visitor

Thank you for the suggestions. 

 

I think the unpivot idea can work and I will attempt to do so.  However when it comes to the actual filtering, I want the user to see all 25 cat columns (regardless of 0 or 1) but to only see those records where the chosen columns contain a 1.  This may be more obvious to me once I have attempted the unpivot ideas.  I will post my findings when done.

On the screenshot you see Cat1,2,25 and 3 because that's all I have in the data, but if I will add more columns to the data then you will see all of them in the filters.

 

Before:

bolfri_0-1671640337677.png

bolfri_1-1671640357177.png

 

After adding new KPIS:

bolfri_2-1671640393495.png

 

bolfri_3-1671640467214.png

 





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

Proud to be a Super User!




bolfri
Super User
Super User

This solution is then you're expecting more KPI columns in the  future.

 

Step 1. Select Record ID and Person (and any other columns that be added and it's strict to that record)

Step 2. Unpivot other columns

bolfri_0-1671638779375.png

 

Use Attribute as a filter.

bolfri_1-1671639290963.png

And set up a filter where Value is not 0

bolfri_2-1671639313247.png

 





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

Proud to be a Super User!




Thank you so much for the pointer. 

I basically used this approach but did not unpivot my main table.  I created a new filtered table in DAX that consisted of the record Id and the 25 cat columns then unpivoted that.  The secret here is that when creating a Dax table there is no easy way to unpivot (because it does not appear on the list of tables that can be pivoted), so I crreated a table for each cat column and unioned them - then filtered to the records where the value was = 1. 

I then used that table as a slicer and made the relationship bi-directional so the filter now works as expected.

 

The code for the new DAX table looks similar to:

 

Anomalies = Filter(UNION(
    SELECTCOLUMNS(MainTable, "RecordID", MainTable[RecordID]
		,"Anomaly", "Cat1"
		, "RowValue", MainTable[Cat1])
    SELECTCOLUMNS(MainTable, "RecordID", MainTable[RecordID]
		,"Anomaly", "Cat2"
		,"RowValue", MainTable[Cat2])
    SELECTCOLUMNS(MainTable, "RecordID", MainTable[RecordID]
		,"Anomaly", "Cat3"
		,"RowValue", MainTable[Cat3])
    ...
    SELECTCOLUMNS(MainTable, "RecordID", MainTable[RecordID]
		,"Anomaly", "Cat25"
		,"RowValue", MainTable[Cat25])
      ), [RowValue] = 1)

 

djurecicK2
Super User
Super User

Check out what @johnt75 posted. As an alternative, you could try using the Filters pane with Cat 1- Cat 25 instead of a slicer.

johnt75
Super User
Super User

I would change the data model so that instead of being in columns the categories are in rows, so that each row would have record number, person, category and value. each record would have multiple entries, potentially one for each category. You could do this either in SQL or in Power Query, select the record number and person columns and then "unpivot other columns".

Once you have that you can create a table with all the categories to use as a slicer, and link that in a one-to-many relationship with the main table.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.