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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Distinct count of ID not working properly in PIE chart.

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.
table.png

 

 

 

 

 

 

 

 

 

 

Thanks

Praneeth.

5 REPLIES 5
v-qiuyu-msft
Community Support
Community Support

Hi @Anonymous,

 

You can create a measure below: 

 

TicketCount = CALCULATE(COUNT(Table1[status]), FILTER(ALLSELECTED(Table1),'Table1'[status]=MAX('Table1'[status])))
 
q1.PNGq2.PNG
 
Please see attached pbix file. 
 
Best Regards,
Qiuyun Yu 
Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

 

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))

q1.PNGq2.PNG

 

 

For more information, see attached pbix. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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.
table.png
 
 
 
 
 
 
 
 
 
 

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
Not applicable

Can any body solve this issue.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors