How to Countrows of repeating values across multiple columns

Question

So I am trying to analyze the dataset above. What I want to do is to count how movie fall in each genre.

Each animation has one column for genre. In that "Genre" column has multiple values so what I did was split that Genre column then I end up with 7 column for genres

Action = 
COUNTROWS(
    FILTER(
        Animation_Movies, 
        Animation_Movies[Genre.1] = "Action" || 
        Animation_Movies[Genre.2] = "Action" || 
        Animation_Movies[Genre.3] = "Action" || 
        Animation_Movies[Genre.4] = "Action" || 
        Animation_Movies[Genre.5] = "Action" || 
        Animation_Movies[Genre.6] = "Action" || 
        Animation_Movies[Genre.7] = "Action"
    )
)

I used the query above to countrow But it counts only the first genre column "Genre.1", The idea is I want it to count "Action" in each column and combine the amount all together.

Please help thank you

Answer 1

You'd rather unpivot these columns in Power Query so that you have one single column containing all genres. This is the shape Power Pivot is designed for.
=> This will make the DAX measure trivial and you'll be able to use some slicer, etc.