Dax Script

[Anonymous]

hello all, 

i'm wondering if someone can help m, being trying to make this measure since the last 1 week now 

my client asked me to produce a dashboard displaying only the last message received

here is the scenario.

its a reservation system, users have id call PDL and eache PDL can have one or multiple message when booking an appointement.

types of message:

- Prise de rdv en succès

-proposition possible

pas de possibilité de rdv 

etc..

now the mesure that i need to create is the count of all last message per max(hour).

i made the following script 

test_mesure:=CALCULATE( DISTINCTCOUNT('ANALYSE PRV MESURES'[PDL]); LASTNONBLANK('ANALYSE PRV MESURES'[DATETIME];'ANALYSE PRV MESURES'[DATETIME])

)

and this is not working. 

someone has an idea on how to make it ?

[Anonymous]

Hi @Anonymous 

Could you please share sample dataset and expected output.

Thanks,

pravin

[technolog · Accepted solution]

Below is a robust measure that does exactly that. It also works when you put Message on rows in a visual. The measure always chooses the last message per PDL inside the latest hour without letting the Message filter change which row is last. Replace 'ANALYSE PRV MESURES' with your table name if needed, and replace [MESSAGE] with the real message column.

Last messages in latest hour

LastMessagesLatestHour =
VAR lastTime =
CALCULATE(
MAX('ANALYSE PRV MESURES'[DATETIME]);
ALLSELECTED('ANALYSE PRV MESURES')
)
VAR lastHourKey = INT(lastTime * 24)
VAR hourRowsNoTypeFilter =
CALCULATETABLE(
FILTER(
ALLSELECTED('ANALYSE PRV MESURES');
INT('ANALYSE PRV MESURES'[DATETIME] * 24) = lastHourKey
);
REMOVEFILTERS('ANALYSE PRV MESURES'[MESSAGE])
)
VAR lastPerPDL =
SUMMARIZE(
hourRowsNoTypeFilter;
'ANALYSE PRV MESURES'[PDL];
"LastTime"; MAX('ANALYSE PRV MESURES'[DATETIME])
)
VAR lastPerPDLJoinReady =
SELECTCOLUMNS(
lastPerPDL;
"PDL"; 'ANALYSE PRV MESURES'[PDL];
"DATETIME"; [LastTime]
)
VAR lastRows =
NATURALINNERJOIN(
hourRowsNoTypeFilter;
lastPerPDLJoinReady
)
VAR typeFilter = VALUES('ANALYSE PRV MESURES'[MESSAGE])
RETURN
IF(
ISEMPTY(typeFilter);
COUNTROWS(lastRows);
COUNTROWS(
FILTER(
lastRows;
'ANALYSE PRV MESURES'[MESSAGE] IN typeFilter
)
)
)