Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Order | Status 1 | Month 1 | Status 2 | Month 2 |
1 | Open | 11 | Closed | 11 |
2 | Closed | 10 | Open | 10 |
3 | Pendent | 9 | Open | 10 |
4 | Open | 4 | Pendent | 9 |
5 | Open | 7 | Open | 7 |
Example: If I select Month 10 in the slicer I should get:
Order | Status 1 | Month 1 | Status 2 | Month 2 |
2 | Closed | 10 | Open | 10 |
3 | Pendent | 9 | Open | 10 |
Any thoughts?
Thank you in advance.
Solved! Go to Solution.
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.
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)
4. Finally, you will get expected result as follows. You can download the.pbix file for more details.
Best Regards,
Angelia
I think I might have find a possible solution.
1. Create a calculated column that concatenates Month1 and Month2:
2. Create a Month table with all combination possible:
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:
4. Use the second column from the Month table as slicer:
Thank you for your reply, however, it doesn't work when you input all the possibilities, there are intersections...
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.
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)
4. Finally, you will get expected result as follows. You can download the.pbix file for more details.
Best Regards,
Angelia
@v-huizhn-msft Can this be done on multiple values too?
For example - I would like to get all rows that contain 9 OR 10
What If I want to select multiple columns and see the intersect of the selected columns?
How can I do multiple multiple selections in the slicer and view the result??
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.
Hi @marcusvmc
One way is to unpivot the months then use a slicer from the unpivoted months columns
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |