cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
patri0t82
Helper III
Helper III

Measure to combine multiple columns for filtering

Hi, in my table, I have several columns that I would like to be able to combine and then filter from. These are the column names:

 

Barrier / Tape / Tags
Damaged Equipment
Fall Protection
Garbage / Debris
Gear / Debris on Deck
Head / Knee Banger
Hoses / Cords (Obstructing Path)
Ice Patches
Ladder / Stair Access
Lighting
Liquid on Ground / Spill
More Safe Behaviours
Nails in Wood / On Ground
Neatness
Overhead Ice
Potential Falling Object(s) Removed
Potential Pinch Points
PPE
Product on Ground
Product Storage / Labeling
Rocks on Road / Walkway
Safety Equipment
Scaffold (Adjust / Neatness)
Sharp Object / Cladding
Signage
Steam / Product Exposure
Uneven Ground / Tripping Hazard
Vehicles
Walkways Clear
Weather Related Behaviours

 

Is there a simple way to create a measure that will combine the results from each column?

1 ACCEPTED SOLUTION

@patri0t82

Please follow the guide in the knowledge base below to create table and measure .

https://community.powerbi.com/t5/Community-Knowledge-Base/Dynamic-column-based-on-slicer-selection/t...

Regards,
Lydia

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

View solution in original post

10 REPLIES 10

@patri0t82 This would be pretty straightforward in the Query Editor with the options to combine columns. But the DAX method has been answered here.- https://community.powerbi.com/t5/Desktop/help-how-to-merge-2-columns/td-p/53920


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hey, thanks for replying. I tool a look at merging the query, but I'm not sure it's the right approach. Under each of those headers, I don't have a count, or number - I have other text strings. I'd like to be able to filter by header and then again count the number of instances for each text string. Does that make sense?

@patri0t82,

Select the columns in your table and click "Merge Column" under Tranform ribbon to merge your columns.
1.JPG

Additionally, I am not clear about your requirement that count number of instances for each text string, could you please share sample data of your table and post expected result?

Regards,
Lydia

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

Hi, thanks for helping me with that, it's certainly helped me achieve my goal! It's combined all the results of those columns so I can choose from one slicer. That's exactly what I needed.

 

Still, I'd also like to be able to combine the headers only from those columns into one slicer. Is there any way to merge column headers?

@patri0t82

Could you please describe more details about "merge headers"? When you use merge columns, the headers are also combined.

Regards,
Lydia

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

Hey, thanks again for responding. In my first post, I listed all the column headers. When I merged the columns, I'm not sure they appeared in the list... Only the results in those columns.

Merry Christmas!

@patri0t82,

What list do you refer to? Could you please post a screenshot of the current status?

Regards,
Lydia

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

I'm sorry it's so hard to explain..

I'll try to word it carefully because I'm not sure what I could take a picture of.

 

I have a series of columns. ie: Scaffolding, PPE, Product on Ground.

 

In the Scaffolding column, I have results like:

Adjusted

Untidy

Tripping Hazard

 

In the PPE column, I have results like:

Needs adjusting

Inadequate for job

Soiled PPE

 

In the Product on Ground column, I have results like:

Oil

Water

Chemical

 

I've been able to successfully merge all the columns together and create one big list of all the individual items:

Adjusted

Untidy

Tripping Hazard

Needs adjusting

Inadequate for job

Soiled PPE

Oil

Water

Chemical

 

What I would like to do, though, is select a column header (one of the three above, for example), and then the individual results from that column. Or, select all of them.

 

Does that make a little more sense?

@patri0t82

Please follow the guide in the knowledge base below to create table and measure .

https://community.powerbi.com/t5/Community-Knowledge-Base/Dynamic-column-based-on-slicer-selection/t...

Regards,
Lydia

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

View solution in original post

Hi again, I'm sorry to re-respond, but that link you provided.. I just found myself more confused when I started working with it. When people fill out their entry form, they can choose from a list of hazards, which are each columns in my table.

While each hazard has its own column, there is also a column records this 'generic' hazard as a record. Ie: vehicles, uneven ground, etc.

 

Major categories in table.PNG

 

I was able to create a new table from these responses somehow, but I'm not sure if it helps:

All Major categories.PNG

 

Now, in my main table, as I said there is a column header for each 'generic' hazard. In those columns are all the 'specific' hazards that people can choose from. Here is an example below:

 

Specific.PNG

 

 

I'd like to create a slicer, somehow. Something that lets me choose a general hazard and then shows me all of the specific hazards within that general hazard that people have entered. I can't figure out how to make them all work together.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors