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
Anonymous
Not applicable

Create a filter out of multiple columns

Hi All,

 

This is my first time posting on here. I have multiple columns with multiple values and I want to use the column headers into the filter by summing up values in partiular column. For example: I want sum of all the values of column freightcharge and use the header freight charge in the filter, then sum all the values in fuel surcharge and use the header in the filter and so on. Any help would be appreciated. Thank you in advance!power bi question.JPG

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Step1: Create multiple measure according to the column name. For example:

           measureA = calculate(sum([column1]),all(table))

           measureB = calculate(sum([column2]),all(table))

           measureC = calculate(sum([column3]),all(table))

           ...etc.

Step2: Create a table contains a column with values like AA, BB and CC ...etc and use this table as slicer.

Step3: Create a measure like below.

           mesaure = Switch(selectedvalue([slicer]),"AA",[measureA],"BB",[measureB],"CC",[measureC]...etc)

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

Thank you so much y'all! It worked 😀

v-jayw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Step1: Create multiple measure according to the column name. For example:

           measureA = calculate(sum([column1]),all(table))

           measureB = calculate(sum([column2]),all(table))

           measureC = calculate(sum([column3]),all(table))

           ...etc.

Step2: Create a table contains a column with values like AA, BB and CC ...etc and use this table as slicer.

Step3: Create a measure like below.

           mesaure = Switch(selectedvalue([slicer]),"AA",[measureA],"BB",[measureB],"CC",[measureC]...etc)

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

Hello, thank you for your reply. Is there any way to pull data directly from the source into the table that I create? Or do i have to put the sum of all the columns manually into the table that i created to use into a filter/slicer?

smpa01
Super User
Super User

@Anonymous  you can't use measures in slicers.

 

But there is a workaround. You can create a disconnected slicer table and use a DAX SWITCH statement to have the preferred measure returned.

 

Pbix is attached

smpa01_0-1637262571313.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

Thank you so much! Can you please guide me stepwise on how to achieve this?

@Anonymous  follow the attached pbix

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

Hi All,

 

It is working but it has a problem. It is fine until i select one field in the slicer but i start selecting multiple fields, the card shows me blank.

 

@smpa01 @v-jayw-msft @StefanoGrimaldi 

 

.1 selection1 selectionMultiple selectionMultiple selection

@Anonymous i am extremely busy this week, can look and reply early next week

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

Hello 🙂 Any updates on this?

StefanoGrimaldi
Resident Rockstar
Resident Rockstar

hey, 

this have little details I recommed you to read the post at the top of the forum about getting your questions aswered quickly, now isnt clear what you want to achieve and how you using the header as filter? cause for what you said you just need to use sum(...) for each column and sum each one up and that it, 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Anonymous
Not applicable

Suppose:

 

I have column A with values 5,10,15,20, column B with values 25,30,35,40, column C with values 45,50,55,60.

 

I want to add 5,10,15,20 in column A, then add 25,30,35,40 in column B and then add 45,50,55,60 from in C.

 

I added values 5,10,15,20 in A and created a new measure AA with total value 50, then added values in B 25,30,35,40 and created a new measure BB with total value 130, and then then added values in C and created another measure CC with total value 210.

 

Now I want to create a filter/slicer where i can filter values from AA,BB and CC.

 

Power BI is not letting me add the new measure in the slicer that i created.

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.