Dynamic distinct counts by group by considering only latest records

[rammishra]

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.

[v-yiruan-msft]

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

[amitchandak · Accepted solution]

@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

[rammishra]

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

[Mi_80]

Hi, sorry Did this get solved?  I have a similar query but I don't see how you find the most recent record?

[rammishra]

Hi,

Please refer to the solution provided by Amit in the thread. The calculation of Measures should provide you the solution. If you are stuck, please share more details to understand it better. An alternate solution could also be achieved by using calculated columns (define a boolean column to check whether the record is 'latest' based on your criteria and then use this column to visualize only latest values on the chart).

[Mi_80]

Unfortunately, I don't understand this solution at all 😞  

Hi, I am trying to produce something similar but this is not working for me at all .  I am told index is not a function?

I have an updates table that update a user status from approved, not approved in categories one and category 2. I also have a calendar table

e.g.

User name	Category 1	Category 2	Created on
user 2	approved	not approved	5/1/23
user 1	approved	not approved	3/1/23
user 2	approved	approved	2/1/23
user 1	not approved	Approved	1/1/23
user 3	not approved	approved	2/1/23

I would like to be able to display a bar chart with a date slider that can show for example the countof category one approved users on a chosen day...so if I filter to 4/1/23 then the count for category 1 approved user would be 2 (users 1and 2) and the count for category 1 not approved would be 1 (user3), count category 2 approved 2 (users 2 and 3), category 2 not approved 1 (user1) as it would only take into account the latest record for a user not the ones previously.  So data table filtered to 4/1/23 as below

UserName	Category 1	category 2	latest Created on
user 2	approved	approved	2/1/23
user 1	approved	not approved	3/1/23
user 3	not approved	approved	2/1/23

and bar  chart as 

So far I have this measure w

Measure =

CALCULATE(FIRSTNONBLANK(new_partnerlandscapehistory[createdon],1),

FILTER(new_partnerlandscapehistory,new_partnerlandscapehistory[createdon] = MAX(new_partnerlandscapehistory[createdon])))

which manages to give me the last record up to that date in a table but can't get this to work with a count in barchart as then it counts the previous records also