Get only Latest records from Group of records as date slicer change and visualize it in Column chart

[Anonymous]

Hello Everyone,

I need your help or idea or any working PBI file to solve one issue.

Here is the information on my problem statement

In my transaction table i have records of the patientes who go for regular checkup to clinic, when we change date range then from chosen period we have to consider only latest/last record of that person from group of records.

This data will change whenever we change time period at runtime and show latest record of all the persons in that period.

Using this derived data i like to create Column chart using several fields.

I hope i am able to explain correctly, please let me know if any more clarity is required.

Kindly do the needful.

Best Regards

Mubin

[amitchandak]

@Anonymous , Try a mesure like

Measure =
VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[date] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
RETURN CALCULATE ( count('Table'[ID] ), VALUES ('Table'[ID ),'Table'[ID] = __id,'Table'[date] = __date )

[Anonymous]

Thanks for the reply Amit,

i had created below measures where

1.

2_Latestkey =trying to find latest key value of the reocrd from the group of records within date range.

2.

COUNT_LATEST = prepare count of latest records

-----------------------------------------------------------------------------

1st Meaure

2_Latestkey =
VAR latest_date =
CALCULATE ( MAX ( FACT_LAB_RESULTS_OBX_A1C[CREATE_TIMESTAMP] ),FILTER(ALLSELECTED(FACT_LAB_RESULTS_OBX_A1C),FACT_LAB_RESULTS_OBX_A1C[PERSON_ID]=MAX(FACT_LAB_RESULTS_OBX_A1C[PERSON_ID])))

VAR person_id =
CALCULATE ( MAX ( FACT_LAB_RESULTS_OBX_A1C[PERSON_ID] ),FILTER(ALLSELECTED(FACT_LAB_RESULTS_OBX_A1C),FACT_LAB_RESULTS_OBX_A1C[PERSON_ID]=MAX(FACT_LAB_RESULTS_OBX_A1C[PERSON_ID])))
RETURN
CALCULATE (
MAX ( FACT_LAB_RESULTS_OBX_A1C[UNIQUE_OBX_KEY_A1C]),
FILTER ( ALL( FACT_LAB_RESULTS_OBX_A1C), FACT_LAB_RESULTS_OBX_A1C[CREATE_TIMESTAMP] = latest_date && FACT_LAB_RESULTS_OBX_A1C[PERSON_ID]=person_id )
)
------------------------------------------------------------------------------------
2nd Measure
COUNT_LATEST = CALCULATE(DISTINCTCOUNT(FACT_LAB_RESULTS_OBX_A1C[PERSON_ID]), FILTER((FACT_LAB_RESULTS_OBX_A1C), FACT_LAB_RESULTS_OBX_A1C[UNIQUE_OBX_KEY_A1C] = [2_Latestkey]))

-------------------------------------------------------------------------------------
3. Diagnose Category as per their observe value of diabetes

2_Latest_Diagnosis =

VAR latest_date =
CALCULATE ( MAX ( FACT_LAB_RESULTS_OBX_A1C[CREATE_TIMESTAMP] ),FILTER(ALLSELECTED(FACT_LAB_RESULTS_OBX_A1C),FACT_LAB_RESULTS_OBX_A1C[PERSON_ID]=MAX(FACT_LAB_RESULTS_OBX_A1C[PERSON_ID])))

VAR person_id =
CALCULATE ( MAX ( FACT_LAB_RESULTS_OBX_A1C[PERSON_ID] ),FILTER(ALLSELECTED(FACT_LAB_RESULTS_OBX_A1C),FACT_LAB_RESULTS_OBX_A1C[PERSON_ID]=MAX(FACT_LAB_RESULTS_OBX_A1C[PERSON_ID])))
VAR latest_value =
CALCULATE (
MAX ( FACT_LAB_RESULTS_OBX_A1C[OBSERV_VALUE]),
FILTER ( ALL( FACT_LAB_RESULTS_OBX_A1C), FACT_LAB_RESULTS_OBX_A1C[CREATE_TIMESTAMP] = latest_date && FACT_LAB_RESULTS_OBX_A1C[PERSON_ID]=person_id )
)
RETURN
IF(ISBLANK(latest_value),"", if(latest_value<=5.7,"5.7 or less - Normal", if(latest_value>5.7 && latest_value<=6.4,"5.8 to 6.4 - PreDiabetic","6.5 or Higher - Diabetic")))
----------------------------------------------------------------------------------
i can not drop measure 2_Latest_Diagnosis on x axis of column chart becuase it return text values and visualize count_latest breakup against it.

It will be good if you throw some light on this.