How can I create these slicers?

[Anonymous]

Hi All!

First of all, I only started using PowerBI 2 days ago so my description is not going to be professional.

I would like to recommend movies based on the user's gender, age, genre preference, etc

My pre-generated data set includes information about the avg. rating of movies by the voter's gender and age. Here are a few examples (every gender - age combination is covered in the data set):.

1. Any Gender- Age 31-45 - avg.rating
2. Females - All Ages - avg.rating
3. Males - Age under 18 - avg.rating
4. avg.rating (in case of unspecified Gender and All Ages)

I wonder if it is possible to create slicers for Gender and Age with the following logic:

• if I select male, and no age groups, it uses the values in the Males - All Ages - avg.rating column
• if I select female and under 18 age group, it uses the Females - Age under 18 - avg.rating column
• if I select no gender and Age 31-45, it uses the Any Gender - Age 31-45 - avg.rating column
• and so on for all combinations

I know its a pretty unprofessional way to describe my problem, but I hope you can still understand what I want to achive.

.

Any help would be appreciated!

Thanks!

[v-jingzhang · Accepted solution]

Hi @Anonymous 

It is possible to achieve. But you need to transform the data first to make it more suitable for slicers.

First in Power Query Editor, make all data in the column have the same format and split them into separate columns Gender, Age and Rating.

After applying data into model, create a measure which is used to display Rating value.

Rating to display = 
MAXX (
    FILTER (
        MovieRating,
        MovieRating[Gender] = SELECTEDVALUE ( MovieRating[Gender], "Any Gender" )
            && MovieRating[Age] = SELECTEDVALUE ( MovieRating[Age], "All Ages" )
    ),
    MovieRating[Rating]
)

Create two slicers, put Gender column and Age column in each. Put above measure in a card visual to display the value.

Here is a DEMO pbix you can download for details. Let me know if you have any questions.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

Thank you very much Jing!