How to make slicer pick value from either of two columns

[Nankaina]

I have a column of categorical values, by which I would like to filter with Slicer. In about 99% of cases, there is only one value to put in this column for each row. However, very rarely, I will need to put two different values in. For example, I'll use countries: most people have citizenship in only one country, but occasionally people will have residence in 2 different countries.

I want to be able to slice by Country, so that if someone has citizenship in both countries, they will come up in either one when selected by the Slicer. I considered making two rows, but then if you select both Countries, the person is double-counted. For things like averages, this can get very problematic very quickly.

I also considered having a second column, but then I would have two separate slicers. The first slicer would only filter by whichever Country is written first, and the second column would only filter by the few values that are in the second column.

Is there a way to combine these? So that with a single slicer, I can select a value and, if that value is in either of the two columns, it will include that row in the selection?

[bolfri]

Can you share a sample row that you have? I know what you're trying to do and I can use dummy data, but I think it will be the best for you share some sample records so I can use similar names. It's not possible - let me know.

[Nankaina]

Unfortunately I cannot share any of the actual data I use, but I've created a dummy document using a really good example which came to my mind after the fact: Pokemon types. 

The file is at this link. I've put the types in two columns - notice how some of them have two types, and others only have one. I will accompany this with a direct question you can answer, which will translate perfectly to the real data:

Do Rock-type pokemon, on average, have a higher Attack stat than the population average?

[bolfri]

Can you put that file on https://wetransfer.com/ ? I don't have an access to your google drive. 🙂

[Nankaina]

Oops, hadn't thought about that! How's this?

[bolfri · Accepted solution]

Yeah, that was fun! 🙂 

The answer is YES, Rock Type pokemons has an Attack avg of 92 and the avg population is 67, which is 14 p. higher.

How it was created?

Warning! This is not the best solution during the fact that the data hold a nessesery information in the columns, not in rows, but it solves your problem with two types of pokemon. 

I will prepare best solution right now to show you how it suppose to look like.

dim_type - provides an information about unique types that can appear in the Type 1 and Type 2 column

dim_attribute - holds an information about attributes, but it needs to be provided manually

DAX - is a tablle with one column and one row to hold all the measures

Measures:

SelectedAttribute = SELECTEDVALUE(dim_attribute[attribute])

_Attribute Value =

SWITCH([SelectedAttribute],

    "Attack",SUM('Sample Data Pokemon'[Attack]),

    "Defense",SUM('Sample Data Pokemon'[Defense]),

    "HP",SUM('Sample Data Pokemon'[HP]),

    "Sp. Attack",SUM('Sample Data Pokemon'[Sp. Attack]),

    "Sp. Defense",SUM('Sample Data Pokemon'[Sp. Defense]),

    "Speed",SUM('Sample Data Pokemon'[Speed])

)

Pokemons =

CALCULATE(

    DISTINCTCOUNT('Sample Data Pokemon'[Pokemon]),

    OR(

        'Sample Data Pokemon'[Type 1] in VALUES(dim_type[Type]),

        'Sample Data Pokemon'[Type 2] in VALUES('dim_type'[Type])

    )

)

Avg attribute =

CALCULATE(

    DIVIDE([_Attribute Value],[Pokemons]),

    OR(

        'Sample Data Pokemon'[Type 1] in VALUES(dim_type[Type]),

        'Sample Data Pokemon'[Type 2] in VALUES('dim_type'[Type])

    )

)

Average attribute in population =

CALCULATE(

    [Avg attribute],

    ALLSELECTED(dim_type),

    ALLSELECTED('Sample Data Pokemon')

)

Difference vs population avg =

IF(

    NOT(ISBLANK([Avg attribute])),

    [Avg attribute] - [Average attribute in population]

)

Link to source file: https://we.tl/t-sDR0J9gRoN

Link to pbix file - Pokemon v1 (without modeling data): https://we.tl/t-C4tHHSscbH
Link to pbix file - Pokemon v2 (with modeling data): https://we.tl/t-P341eGPFIw

[bolfri]

Second, but correct version of pokemon dashboard that solving all the issue.

Data preparation

dim_pokemon

In the data that we have we are adding an index and create a table with distinct value for each pokemon.

fact_types

From that same data we are taking Index, Type 1 and Type 2.

Then we are unpivoting other Columns than Index.

We are removing all blank fields.

and removing attribute column.

dim_type

Based on fact_types we are preparing our dim table for pokemon types.

fact_attributes

From our oryginal table we are selecting Index and columns that contains infromation about attributes.

We are unpivoting anything else than Index.

dim_attribute

From the fact_attibute table we are creating distinct table for attributes.

Final step is to disable "Enable load" option on the Sample Data Pokemon, so we won't have this table in the Power BI report. This table is needed only to prepare other tables. Note that in this scenario you are refreshing one source and the rest of them update automaticly.

Data modeling

Now we can connect our tables with total logic. I put here all the relationships that we need to let Power BI works for us without any problems.

Measures

You can compare those measures to previous one. For me this is much easier to understand whats goin on. In the dashboard there are also 3 bonus measures that without filtering showing same results (calld static/all pokemons), but after putting a filter you can see difference.

Basic measures

SelectedAttribute = SELECTEDVALUE(dim_attribute[Attribute])

Pokemons = DISTINCTCOUNT(dim_pokemon[Index])

Average attribute (selected pokemons) = AVERAGE(fact_attributes[Value])

Average per population

Average attribute in population (dynamic) = CALCULATE([Average attribute (selected pokemons)],ALLSELECTED(dim_pokemon),ALLSELECTED(dim_type))

Difference versus population

Difference vs all selected =

IF(

    NOT(ISBLANK([Average attribute (selected pokemons)])),

    [Average attribute (selected pokemons)] - [Average attribute in population (dynamic)]

)

Final result

On the second screen you can see the difference between dynamic and static measures, so in the pokemon table you are not only comaring results to Rock Pokemons but also all pokemons.

I hope that will solve your issue. You can choose first or second solution. The one that suits your need. 🙂 Have a nice day!

Link to source file: https://we.tl/t-sDR0J9gRoN

Link to pbix file - Pokemon v1 (without modeling data): https://we.tl/t-C4tHHSscbH
Link to pbix file - Pokemon v2 (with modeling data): https://we.tl/t-P341eGPFIw

[Nankaina]

Not only did you make TWO functional solutions, you went the extra mile and made a full report, complete with a pretty background! I love this, thank you so much for putting a smile on my face and a solution on my query.

Based on the tables you made, it looks like the easier way to store this data going forward is in two distinct rows that share an index value. I'll take that to heart as well and see if we can swap to storing it that way.

[bolfri]

Happy to hear that I can make someone smile by doing something I love. By helping others here I practise both: Power BI & language skills. That's why I put so many screenshots. 😂

If something isn't clear or doesn't work properly, just ask. Have a nice day!

[AllisonKennedy]

@Nankaina 

You will need two rows to make this work, but might consider having two tables with the different levels of granularity to ensure your averages can be more easily calculated, or use the VALUES() function and AVERAGEX to help calculate the correct averages. 

https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html

For example, use a distinctcount: 

Total Citizens =

distinctcount( Citizens[Name])

and use that in your measures.