One slicer to control two columns at the same time

[Luden76]

Hello Guys,

I'm quite new to power bi. I have just one lookup table and multiple fact tables and their relationships are all one to many. I want to create a slicer using category column in the lookup table to filter one specific table based on either category or old category columns. Also this slicer is filtering other visuals as a normal filter.

For example if you select golf, this one specific table is going to show category = golf or old category = golf.

So I'm thinking that I can create a measure counting rows based on the criteria above and get 1 if true for each row and filter that measure = 1

I created this measure:

PickThis =

var _selectedCategory = SELECTEDVALUE(MA_Lookup[Category Desc])

return

CALCULATE(COUNTROWS(MA_Up_Downgrade) , FILTER(ALL(MA_Up_Downgrade),MA_Up_Downgrade[Category Desc] = _selectedCategory || MA_Up_Downgrade[Old Category] = _selectedCategory ))

So basically if either column is golf, then count each row as 1. Then later I can filter this measure to 1 to achieve my goal. However, if I select golf, it's giving me total numbers for each row instead of 1 if the condition is true. Also if nothing is selected, it's showing blank, but I need the table to show all.

P.S. I have tried creating a diconnected slicer table including all categories and it worked, but in the ideal situation, I only need one slicer only from lookup table instead of two.

Here is the sample file.

https://drive.google.com/file/d/15gVjklMerU-AgayrmMqezptByh3tQq51/view?usp=sharing 

[DataInsights]

@Luden76,

Try this solution. You can put the measure in a visual filter.

https://community.powerbi.com/t5/Desktop/Once-Slicer-for-multiple-columns/m-p/430979#M198545 

[Luden76]

@DataInsights 

Hi, thanks for your reply. I took a look at the solution and seems like you have to created a disconnected slicer table with no relationships to any table. So it's  impossible to just use the one column from the lookup table as a slicer to control two columns in the fact table while still have a one to many relationship between them?

[DataInsights]

@Luden76,

Here's an alternative that allows you to keep the slicer connected to the data model. Create a calculated table that is a summary of the fact table (summarize by the columns displayed in the visual):

SummaryTable = SUMMARIZE ( MA_Up_Downgrade, MA_Up_Downgrade[Category Desc], MA_Up_Downgrade[Old Category], MA_Up_Downgrade[Type] )

This table has no relationships. Add columns from this table to the visual. Create measure:

Filter Visual = 
VAR vCategorySelected =
    VALUES ( MA_Lookup[Category Desc] )
VAR vResult =
    CALCULATE (
        COUNTROWS ( SummaryTable ),
        FILTER (
            SummaryTable,
            SummaryTable[Category Desc]
                IN vCategorySelected
                || SummaryTable[Old Category] IN vCategorySelected
        )
    )
RETURN
    vResult

Filter the visual where this measure = 1.

-------------------------------------------------------------

The link below is to a similar post. The difference is that no additional fact table columns are in the visual. Since a slicer often controls multiple visuals, it's preferable to keep the slicer connected to the data model and create a disconnected summary table for special cases.

https://community.powerbi.com/t5/Desktop/Filter-Table-by-Column-Values-Associated-with-Selected-Slic...