cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

A slicer to use in multiple columns

Hello to all

 

I have the following table:

C1     C2     C3    C4

a

b       a   

a       b       c

d

b      c        d      a

 

How can I slice it to find the rows that have a a? (in this case 1,2,3,5)

 

I saw some post sugesting a union first to use on the slicer but I do no know how many Columns I have because the table is build using a power query function.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
erik_tarnvik Established Member
Established Member

Re: A slicer to use in multiple columns

Understand. In that case the approach mentioned by @@Rodrigo_Carva may be worth trying. Use Power Query to merge all pertinent columns in your table into one single column and use that column to determine the precense of the selected value. I.e your table becomes

C1     C2     C3    C4      Combined

a                                   a

b       a                          ba

a       b       c                 abc

d                                   d

b      c        d      a         bcda

 

Then adjust the measure I suggested to:


Measure = IF(HASONEVALUE(Params[Value]),
IF(FIND(MAX(Params[Value]), MAX(TT[Combined]),1,0) <> 0, 1, 0), 1)

 

 

 

View solution in original post

4 REPLIES 4
Rodrigo_Carvalh Frequent Visitor
Frequent Visitor

Re: A slicer to use in multiple columns

If you do not know the number of columns I just can think about creating on Power query a column that says if some of the built columns has an "a". Other option would be to have a column on Power query that concatenates the other columns and treat that on DAX finding an "a" on it.

erik_tarnvik Established Member
Established Member

Re: A slicer to use in multiple columns

Create a paramter table Params that has one column Value:

Value
a
b
c
d


Make sure this table does not have a relationship with the other table. Place Params[Value] in a slicer. Create a measure as follows (TT is your original table):

Measure = IF(HASONEVALUE(Params[Value]), 
	         IF(MAX(TT[C1]) = MAX(Params[Value]) ||
		    MAX(TT[C2]) = MAX(Params[Value]) ||
		    MAX(TT[C3]) = MAX(Params[Value]) ||
		    MAX(TT[C4]) = MAX(Params[Value]),
		    1,
		    0), 
	  1)

In your visual, place this measure among the filters for your table and enter Show Items When Value is 1. Now if you select "a" in your slicer, your table will only show rows where the measure evalutes to 1 and you should have achieved your objective.

 

Anonymous
Not applicable

Re: A slicer to use in multiple columns

@erik_tarnviktried your solution and almost solved my problem. In fact I do not know the number of columns my table has because it is build using a power query function. How can we change the measure to make reflect the case we have more than 4 columns?

 

erik_tarnvik Established Member
Established Member

Re: A slicer to use in multiple columns

Understand. In that case the approach mentioned by @@Rodrigo_Carva may be worth trying. Use Power Query to merge all pertinent columns in your table into one single column and use that column to determine the precense of the selected value. I.e your table becomes

C1     C2     C3    C4      Combined

a                                   a

b       a                          ba

a       b       c                 abc

d                                   d

b      c        d      a         bcda

 

Then adjust the measure I suggested to:


Measure = IF(HASONEVALUE(Params[Value]),
IF(FIND(MAX(Params[Value]), MAX(TT[Combined]),1,0) <> 0, 1, 0), 1)

 

 

 

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.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 47 members 1,027 guests
Please welcome our newest community members: