topic Re: Dax Script in DAX Commands and Tips

Dax Script

Anonymous — Thu, 12 Mar 2020 09:39:03 GMT

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 ?

Re: Dax Script

Anonymous — Thu, 12 Mar 2020 12:26:23 GMT

Hi Anonymous

Could you please share sample dataset and expected output.

Thanks,

pravin

Re: Dax Script

technolog — Sun, 12 Oct 2025 13:33:41 GMT

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
)
)
)