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

Filter table based on two columns

Hi everyone,

 

I have a huge table which is simplified below.

I need to select a month in a slicer and filter 2 columns in that table, meaning that the filtered table needs to contain entries with at least one month (Month 1 or Month 2) as selected in the slicer.

The problem is, the same entry can have two months associated with it.

 

OrderStatus 1Month 1Status 2Month 2
1Open11Closed11
2Closed10Open10
3Pendent9Open10
4Open4Pendent9
5Open7Open7

 

Example: If I select Month 10 in the slicer I should get:

 

OrderStatus 1Month 1Status 2Month 2
2Closed10Open10
3Pendent9Open10

 

Any thoughts?

 

Thank you in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Filter table based on two columns

Hi @marcusvmc,

Please follow the steps below and get expected result. I named your sample table as 'Test1'

1. Create a new table including unique month value by type the formula.

Month =
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Test1, "Month", Test1[Month 1] ),
        SELECTCOLUMNS ( Test1, "Month", Test1[Month 2] )
    )
)


You will get the Month table, please note don't create relationship betweem the Month table and your fact table.

1.PNGMonth
2. Create two measures to get Month1, Month2 columns using the formulas.

Month1 = CALCULATE(VALUES(Test1[Month 1]),FILTER(Test1,Test1[Month 1]=SELECTEDVALUE('Month'[Month])))

Month2 = CALCULATE(VALUES(Test1[Month 2]),FILTER(Test1,Test1[Month 2]=SELECTEDVALUE('Month'[Month])))


3. Create a table visual to display all rows including selected month in Month1, Month2. Create a measure to filter the table, add it as visual level filter, please see the screenshot shown.

Filter = IF(Test1[Month1]||Test1[Month2],1,0)

2.PNGAdd measure [filter] as visual level filter
4. Finally, you will get expected result as follows. You can download the.pbix file for more details.

3.png4.PNG

 

Best Regards,
Angelia

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Filter table based on two columns

Hi @marcusvmc

 

One way is to unpivot the months then use a slicer from the unpivoted months columns

fabianbram Frequent Visitor
Frequent Visitor

Re: Filter table based on two columns

I think your only option here is to create two dimensions for months and connect them to the months columns. But with this solution you would have to select the same month twice in the slicers.

v-huizhn-msft Super Contributor
Super Contributor

Re: Filter table based on two columns

Hi @marcusvmc,

Please follow the steps below and get expected result. I named your sample table as 'Test1'

1. Create a new table including unique month value by type the formula.

Month =
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Test1, "Month", Test1[Month 1] ),
        SELECTCOLUMNS ( Test1, "Month", Test1[Month 2] )
    )
)


You will get the Month table, please note don't create relationship betweem the Month table and your fact table.

1.PNGMonth
2. Create two measures to get Month1, Month2 columns using the formulas.

Month1 = CALCULATE(VALUES(Test1[Month 1]),FILTER(Test1,Test1[Month 1]=SELECTEDVALUE('Month'[Month])))

Month2 = CALCULATE(VALUES(Test1[Month 2]),FILTER(Test1,Test1[Month 2]=SELECTEDVALUE('Month'[Month])))


3. Create a table visual to display all rows including selected month in Month1, Month2. Create a measure to filter the table, add it as visual level filter, please see the screenshot shown.

Filter = IF(Test1[Month1]||Test1[Month2],1,0)

2.PNGAdd measure [filter] as visual level filter
4. Finally, you will get expected result as follows. You can download the.pbix file for more details.

3.png4.PNG

 

Best Regards,
Angelia

View solution in original post

fabianbram Frequent Visitor
Frequent Visitor

Re: Filter table based on two columns

I think I might have find a possible solution. 

1. Create a calculated column that concatenates Month1 and Month2:
Screenshot_1.png

2. Create a Month table with all combination possible: 

Screenshot_2.png

 

note that I only included the concatenation 1010 once and offcourse you will have to this 12 times because I only did for october.

 

3. Connect the table with the month table using the concatenation key:

Screenshot_3.png

 

4. Use the second column from the Month table as slicer:
Screenshot_4.png

 

 

marcusvmc Frequent Visitor
Frequent Visitor

Re: Filter table based on two columns

Thank you for your reply, however, it doesn't work when you input all the possibilities, there are intersections...

Highlighted
myou Frequent Visitor
Frequent Visitor

Re: Filter table based on two columns

How can I do multiple multiple selections in the slicer and view the result??

danimelv Regular Visitor
Regular Visitor

Re: Filter table based on two columns

What If I want to select multiple columns and see the intersect of the selected columns?

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: 136 members 1,465 guests
Please welcome our newest community members: