Distinct count of ID not working properly in PIE chart.

[Anonymous]

Hi All,

Please help me in solving issue as attached sample data below.

In a pie chart for details selected status column and value field for distinct count of ID. SLicer also existed for date filter.

For example when I selected date range from jan1 to jan9 count should be 3 for closed status based on max date of each ID.

For example when I selected date range from jan1 to jan4 count should be 2 for closed status(for aa ID) and one opened status(for bb ID) based on max date of each ID.

I used calculated measure 

TicketCount = COUNTX(DISTINCT(Sheet2[ID]),MAX(Sheet2[Date])) for distinct count of ID for their max date.

But when selecting date range from jan1 to jan9 it showing 9 count  in pie chart.

Thanks

Praneeth.

[v-qiuyu-msft]

Hi @Anonymous,

You can create a measure below: 

TicketCount = CALCULATE(COUNT(Table1[status]), FILTER(ALLSELECTED(Table1),'Table1'[status]=MAX('Table1'[status])))

Please see attached pbix file. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi,

Still It showing incorrect result, what I was expecting is If we select datarange from jan1 to jan9 then count of ID's should be 3. How means for aa id max date status is closed, b id max date status is closed and cc id max date status is closed so in closed status it want show 3 count. 

If we select datarange from jan1 to jan5 then count of ID's should be 2. How means for aa id max date status is closed, bb id max date staus is updated, then it should be show closed staus 1count and updated status 1 count.

Thanks

Praneeth

[v-qiuyu-msft]

Hi @Anonymous,

You can modify the measure as below: 

TicketCount = var t=SUMMARIZE(ALLSELECTED('Table1'),'Table1'[ID],"maxStatus",var  d= max(Table1[Date])return CALCULATE(max(Table1[status]),Table1[Date]=d))
var s= max(Table1[status]) 
return COUNTROWS(FILTER(t,[maxStatus]=s))

For more information, see attached pbix. 

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi All,

Please help me in solving issue as attached sample data below.

In a pie chart for details selected status column and value field for distinct count of ID. SLicer also existed for date filter.

For example when I selected date range from jan1 to jan9 count should be 3 for closed status based on max date of each ID.

For example when I selected date range from jan1 to jan4 count should be 2 for closed status(for aa ID) and one opened status(for bb ID) based on max date of each ID.

I used calculated measure 

TicketCount = COUNTX(DISTINCT(Sheet2[ID]),MAX(Sheet2[Date])) for distinct count of ID for their max date.

But when selecting date range from jan1 to jan9 it showing 9 count  in pie chart.

what I was expecting is If we select datarange from jan1 to jan9 then count of ID's should be 3. How means for aa id max date status is closed, b id max date status is closed and cc id max date status is closed so in closed status it want show 3 count. 

If we select datarange from jan1 to jan5 then count of ID's should be 2. How means for aa id max date status is closed, bb id max date staus is updated, then it should be show closed staus 1count and updated status 1 count.

Thanks

Praneeth

[Anonymous]

Can any body solve this issue.