Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Can someone help with DAX Script?

Hello everyone, I am looking for some help in writting a DAX that will give me the following result:

 

Ideal result = calculate number of activity IDs that have changed status from Not Started to In Progress between Sep 15 and Sep 16.

 

Ideally i would like to compare this measure for more than two days and plot the measure over dates that are presneted in Column 1. 

 

Sample Dataset is here:

DateActivity IDActivity Status
15 sep 2021ANot Started
15 sep 2021   BNot Started
15 sep 2021   CNot Started
15 sep 2021   DIn Progress
15 sep 2021   EIn Progress
16 sep 2021   ANot Started
16 sep 2021   BIn Progress
16 sep 2021   CIn Progress
16 sep 2021   DIn Progress
16 sep 2021   EIn Progress

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the link down below, that is the sample pbix file.

     

     

    Ideal result measure : =
    VAR newtable =
    ADDCOLUMNS (
    VALUES ( Data[Activity ID] ),
    "@statuschange",
    CALCULATE (
    IF (
    COUNTROWS ( SUMMARIZE ( Data, Data[Activity ID], Data[Activity Status] ) ) > 1,
    1
    )
    )
    )
    RETURN
    SUMX ( newtable, [@statuschange] )

     

     

    Link to the sample pbix file 

     

     

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    4 years ago
    Status Transition = 
SUMX (
    DISTINCT ( INFO[Activity ID] ),
    0 + ( CALCULATE ( DISTINCTCOUNT ( INFO[Activity Status] ) ) > 1 )
)