Count function in pie chart based on slicer

[Tsysh]

Hello,

I'm trying to make a simple pie char that change based on a slicer selection. In theory is should be easy, but i'm struggling with the count function displaying the correct amount.

Lets say I have a data set like this (Table1):

Firstly i made a new Query with Headers as a column (called Slicer Selection) and used in my slicer, so i have a slicer like this:

• A
• B
• C
• D
• E
• F

Then i made a new measure that runs a count function depending on the slicer selection (called Column Selection):

Column Selection = 
SWITCH (
    TRUE (),
    CONTAINS (
        'Slicer Selection',
        'Slicer Selection'[Column1], "A"
    ),COUNT(Table1[A]), 

    CONTAINS (
        'Slicer Selection',
        'Slicer Selection'[Column1], "B"
    ),COUNT(Table1[B]), 

    CONTAINS (
        'Slicer Selection',
        'Slicer Selection'[Column1], "C"
    ),COUNT(Table1[C]), 

    CONTAINS (
        'Slicer Selection',
        'Slicer Selection'[Column1], "D"
    ),COUNT(Table1[D]), 

    CONTAINS (
        'Slicer Selection',
        'Slicer Selection'[Column1], "E"
    ),COUNT(Table1[E]), 

    CONTAINS (
        'Slicer Selection',
        'Slicer Selection'[Column1], "F"
    ),COUNT(Table1[F]), 

    BLANK()
)

and i made a new query with result options (called Result):

 

In my pie chart, I put the Result column in Legend and the values is my Column Selection.

And here is the part that i'm struggling with. I don't know how to setup my relationship to show the correct value distribution on my pie chart. The way it is right now, its simply counting the rows and showing that every options has the max number.

I know i can take the Result column and make a relationship with column A on table1, but this will work only with column A. I don't know how to make with every column. Maybe there is a easier way to do this whole thing with something like unpivot, but i'm new to pbi and dax, so i'm limited to what i can think its possible.

Any help is really appreciated, thanks!

[parry2k]

@Tsysh awesome, you got the concept, sometimes it is very hard to put each and every little detail. As far as you have the solution, that's what matters.

Cheers!!

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Tsysh you should be unpivoting your data, and then everything will be super easy

Transform data -> Add a index column -> select index column -> right click -> Unpivot other columns

you will get two columns, attribute and value, change it as you see fit and also at this step you can remove index column, since it was required to unpivot the data.

Close and apply

Now you can drop value column on the legend and add a measure called Count

Count = COUNTROWS ( Yourtable )

 and put it on value

you can use attribute for slicer and you are good to go.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Tsysh]

Hi, @parry2k .

Thanks for the reply. 

I tried your solutions and partially worked. If i use the countrow function that you mentioned, it wouldn't  show the correct distribution on my pie chart (each value was getting the max number of rows). Instead i put, in the Values field, the Count of Value from the unpivoted data and now it woks fine.