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



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || Learn 50+ Power Query List Functions
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

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



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || Learn 50+ Power Query List Functions
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

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

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

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.