Pie Chart on the Count of ID based on how many different ID_2

[mellomoon3]

I have the following table:

ID ID_2 Date

1 234 Jan '24

1 829 Feb '24

2 981 Jan '24

2 146 Mar '24

2 286 Nov '24

3 321 May '24

3 743 Feb '24

I want to be able to create a pie chart where it counts the number of ID based on how many different ID_2 there are but take into account for any filter, in this case the date filter.

For example, if we use all the data in the table, there are 2 IDs where count ID_2 = 2 and 1 ID where count ID_2 = 3.

If we limit the date to just Jan '24 - Feb '24 then there are 2 IDs where count ID_2 = 1 and 1 ID where 1 ID where count ID_2 = 2.

How would I write the measure(s) for this so that I can create a pie chart for this?

Appreciate any guidance. Thanks in advance!

[rohit1991]

Hi @mellomoon3 ,
To create a pie chart in Power BI that counts the number of IDs based on the number of distinct ID_2 values while considering date filters, you need to define two DAX measures. The first measure calculates the distinct count of ID_2 per ID, ensuring it dynamically responds to any applied filters. This can be achieved using CALCULATE and DISTINCTCOUNT, preserving the grouping at the ID level while allowing filters such as date selection to take effect. The measure is written as follows:

DistinctID2Count = 
VAR UniqueID2 = CALCULATE(
    DISTINCTCOUNT('Table'[ID_2]),
    ALLEXCEPT('Table', 'Table'[ID])
)
RETURN UniqueID2

Next, a second measure is required to count how many IDs belong to each distinct ID_2 count category, which enables proper grouping for visualization in the pie chart. This measure aggregates the count of IDs for each distinct ID_2 count value, ensuring that it dynamically adjusts when date filters are applied:

IDCountByID2 = 
VAR ID2Groups = SUMMARIZE(
    'Table',
    'Table'[ID],
    "DistinctID2Count", [DistinctID2Count]
)
RETURN COUNTROWS(ID2Groups)

To use these measures in the pie chart, place DistinctID2Count as the category (legend) and IDCountByID2 as the values. This setup ensures that the chart dynamically adjusts to any applied date filter, correctly reflecting the count of IDs based on the number of distinct ID_2 values in the filtered data.

[mellomoon3]

Thank you so much for your help!

I am not sure why, but it is not letting me place DistinctID2Count as the category (legend) in a pie chart. Do you know any reason why that is?

[danextian · Accepted solution]

Hi @mellomoon3 

If you intend to use  the count of ID_2 per ID as a legend or category then you can't be using a measure for that as measures don't have row context and their result are dependen on the dimensions added to a  visual.

This calculated column formula counts the ID_2 for each ID

Count of ID_2 by ID = 
COUNTROWS ( FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ) )

[mellomoon3]

Thank you so much for your help! I tried this and it worked.