Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dax Script

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 ?

 

dax mode count
DAX Query
DAX. CALCULATE

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

     

    Could you please share sample dataset and expected output.

     

    Thanks,

    pravin

  • technolog's avatar
    technolog
    Super User
    10 months ago

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