Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
selpaqm
Helper V
Helper V

How to filter Pie chart with a measure

 

CustomerScoregradeQ1Q2
AA80gold11
BB75silver11
CC80gold11
BB75silver22
CC75silver22
DD80gold22
CC65bronze33
DD75silver33
EE100platinum33

 

I have a table as above. my aim is to compare in 2 pie chart that only customers belongs to compare months.

İ.e If i compare q1 and q2

bb and cc need to be compared because aa is not in q2 and dd is not in q1. and grade need to be  q1 1 silver 1 gold, q2 2 silver

if compare q1 and q3 only cc need to be copared in pie chart.

I have 2 slicers for quarters in my page and can check changes in table but not in pie chart.

selpaqm_0-1680676652739.png

As you can see on power bi pi charts shows 3 customer and comparing them for quarters. however, as you can see on table there are only 2 customer in both quarter and I want to compare them in pie charts. So i need a slicer to see new customers and removed customers and customers that active on both quarter. below you can check the test pbix file.

Test.pbix 

 

1 ACCEPTED SOLUTION
barritown
Super User
Super User

Hi @selpaqm,

I have a super-complicated solution for your problem with 4 additional auxiliary tables.

If you have a small dataset and won't be frightened by the look of this solution, it will do, otherwise - hope someone proposes a cuter solution.

 

Here you can download the PBIX file - https://www.dropbox.com/s/p038xljt4ejghy0/community-2-pies-v2.pbix?dl=0

 

Here's how it works:

1. The initial table is the same but without the Q2 column. It will be added later and will be different from yours.

 

2. We create 2 additional tables with the help of the following [DAX] code:

Augmented Table 1 = 
VAR _tbl = SELECTCOLUMNS ( 'dataset', 
                           "Customer2", [Customer],
                           "Q2", [Q1] )
VAR _hugetbl = CROSSJOIN ( 'dataset', _tbl )
VAR _filter1 = FILTER ( _hugetbl, [Customer] = [Customer2] )
VAR _filter2 = FILTER ( _filter1, [Q2] <> [Q1] )
RETURN _filter2

You'll get something like this:

barritown_0-1681226666589.png

Customer2 is not needed here, it is used only for filtering earlier but I don't remove it in order not to make code even more complicated.

 

3. We create Q1 and Q2 tables which we will use in slicers:

Q1 = VALUES ( 'dataset'[Q1] )

Q2 = SELECTCOLUMNS ( VALUES ( 'dataset'[Q1] ), "Q2", [Q1] )

 

4. We arrange 4 connections between tables in this auxiliary model:

'Q1'[Q1] -> 'Augmented table 1'[Q1]

'Q1'[Q1] -> 'Augmented table 2'[Q2]

'Q2'[Q2] -> 'Augmented table 1'[Q2]

'Q2'[Q2] -> 'Augmented table 2'[Q1]

barritown_1-1681227204532.png

 

5. We use 'Augmented table 1' for the first pie chart and 'Augmented table 2' - for the second.

 

As you can see, the results are the ones you expect to obtain:

barritown_2-1681227412452.png

 

Should you need any clarifications, let me know.

View solution in original post

4 REPLIES 4
barritown
Super User
Super User

Hi @selpaqm,

I have a super-complicated solution for your problem with 4 additional auxiliary tables.

If you have a small dataset and won't be frightened by the look of this solution, it will do, otherwise - hope someone proposes a cuter solution.

 

Here you can download the PBIX file - https://www.dropbox.com/s/p038xljt4ejghy0/community-2-pies-v2.pbix?dl=0

 

Here's how it works:

1. The initial table is the same but without the Q2 column. It will be added later and will be different from yours.

 

2. We create 2 additional tables with the help of the following [DAX] code:

Augmented Table 1 = 
VAR _tbl = SELECTCOLUMNS ( 'dataset', 
                           "Customer2", [Customer],
                           "Q2", [Q1] )
VAR _hugetbl = CROSSJOIN ( 'dataset', _tbl )
VAR _filter1 = FILTER ( _hugetbl, [Customer] = [Customer2] )
VAR _filter2 = FILTER ( _filter1, [Q2] <> [Q1] )
RETURN _filter2

You'll get something like this:

barritown_0-1681226666589.png

Customer2 is not needed here, it is used only for filtering earlier but I don't remove it in order not to make code even more complicated.

 

3. We create Q1 and Q2 tables which we will use in slicers:

Q1 = VALUES ( 'dataset'[Q1] )

Q2 = SELECTCOLUMNS ( VALUES ( 'dataset'[Q1] ), "Q2", [Q1] )

 

4. We arrange 4 connections between tables in this auxiliary model:

'Q1'[Q1] -> 'Augmented table 1'[Q1]

'Q1'[Q1] -> 'Augmented table 2'[Q2]

'Q2'[Q2] -> 'Augmented table 1'[Q2]

'Q2'[Q2] -> 'Augmented table 2'[Q1]

barritown_1-1681227204532.png

 

5. We use 'Augmented table 1' for the first pie chart and 'Augmented table 2' - for the second.

 

As you can see, the results are the ones you expect to obtain:

barritown_2-1681227412452.png

 

Should you need any clarifications, let me know.

@barritown thanks for the solution. it is too complicated but it seems it is working. my dataset has 2k rows and it will be fine.

selpaqm
Helper V
Helper V

@lbendlin thanks for your kind message. However, this is not what I need. I need 2 pie charts with grades. thanks again.

lbendlin
Super User
Super User

you may be overcomplicating things a little. See attached for my version.

lbendlin_0-1681083532218.png

 

Also note that customer C changes grade from q1 to q3.  Not sure how you plan to handle that.

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.