Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Latest Status

Hi ALL

 

We need to show the membership status on the selected transaction date for each user. In another fact table, we have the membership status for specific date ranges and sequences. Finally, we need to show the selected transaction date between which membership status they have and the latest sequence values. If anyone can figure this measure out, please let me know

 




The output should be like this 


 

 

Interesting Links
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    3 years ago

    Anonymous , Create a new column in table 1

     

    New =

    var _max =maxx(filter(Table2, Table1[user_id] = Table2[user_id]  && Table1[Transaction Date] >= Table2[Start Date]

    && Table1[Transaction Date] <= Table2[End Date]) , Table2[Status])

    return

    coalesce(_max, "Inactive")

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , Create a new column in table 1

     

    New =

    var _max =maxx(filter(Table2, Table1[user_id] = Table2[user_id]  && Table1[Transaction Date] >= Table2[Start Date]

    && Table1[Transaction Date] <= Table2[End Date]) , Table2[Status])

    return

    coalesce(_max, "Inactive")

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi amitchandak 

      Will it work For Sequence wise also?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please try below steps:

    1. create a measure with below dax formula

    Measure =
VAR cur_uid =
    SELECTEDVALUE ( Table1[User id] )
VAR max_date =
    MAXX (
        FILTER ( ALL ( Table1 ), Table1[User id] = cur_uid ),
        [Transaction Date]
    )
VAR tmp =
    FILTER ( ALL ( Table2 ), Table2[User id] = cur_uid )
VAR tmp1 =
    ADDCOLUMNS (
        tmp,
        "Flag",
            IF ( max_date >= Table2[From Date] && max_date <= Table2[To Date], 1 )
    )
VAR val =
    CALCULATE ( MAX ( Table2[Sequnence] ), FILTER ( tmp1, [Flag] = 1 ) )
VAR _result =
    CALCULATE (
        MAX ( Table2[Status] ),
        FILTER ( ALL ( Table2 ), Table2[User id] = cur_uid && Table2[Sequnence] = val )
    )
RETURN
    _result

    2. add a table visual with Table1 field and measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.