cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rammishra
Helper I
Helper I

Dynamic distinct counts by group by considering only latest records

Hello friends

 

I am stuck with a problem that looked simple but I am not able to fix it. I seek support from you.

 

I have a table (named FACT) that contains data coming from monitoring visits to health facilities. The key columns in this table are:

FacilityID- a text column taken from the database

Date of visit - Date column taken from the database

Score- a calculated column based on the observations recorded during the visit

Rank category- a calculated column based on the scores. It has values like "Low", "Medium", "High" based on the score column and predefined cutoff values of the scores.

 

I want to create a pie chart that shows the distribution of facilities across ranks based on the most recent visit to the facility. The pie chart should be updated dynamically when I use the date of visit as a slicer. For instance, consider a facility "A" which had a rank of "Low" based on the first visit conducted on 1st Jan. The same facility was visited on 10th Jan and ranked "Medium". If I select the date range, from 1st Jan to 10th Jan, then this facility should only be counted once with rank "Medium" in the pie chart (as that is the latest observation). If I select the date range from 1st Jan to 9 Jan, then this facility should be counted with rank "Low" (as that is the most recent observation for this facility in the date range specified).

 

I could identify the latest records using calculated columns and used visual level filter but this did not work as calculated columns do not update their values based on slicers. 

 

Thanks in advance for your help.

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@rammishra , This will not help in the case of the calculated column.

Both score and rank category need to be measure.

Then you need an independent table to have values of the rank category ("Low", "Medium", "High") , join the column of this table with your rank category  measure in a new measure with a group by FacilityID

 

measure like, M2 , you have to use

M1= sumx(values(Table[FacilityID]),[score])

M2=  calculate([M1], filter(Table, [rank category] = max(category[category]))

 

 

refer my blog/ video for more detailed steps

 


Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k
#PowerBI5 #DynamicSegmentation #Bucketing #Binning #powerbiturns5 #kanerika #bu

View solution in original post

3 REPLIES 3
yingyinr
Community Support
Community Support

Hi @rammishra ,

Whether your problem has been resolved? If no, could you please share some sample data in your FACT table and expected result with specific example and backend logic in order to provide you a suitable solution? Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@rammishra , This will not help in the case of the calculated column.

Both score and rank category need to be measure.

Then you need an independent table to have values of the rank category ("Low", "Medium", "High") , join the column of this table with your rank category  measure in a new measure with a group by FacilityID

 

measure like, M2 , you have to use

M1= sumx(values(Table[FacilityID]),[score])

M2=  calculate([M1], filter(Table, [rank category] = max(category[category]))

 

 

refer my blog/ video for more detailed steps

 


Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k
#PowerBI5 #DynamicSegmentation #Bucketing #Binning #powerbiturns5 #kanerika #bu

Thanks, Amit. Great help. Thanks for always being so helpful.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.