Forum Discussion

jiji's avatar
jiji
Frequent Visitor
3 years ago
Solved

Unchanged Status over multiple weeks

Hi guys, i have a table with 3 columns; Names, Status, date(8 weeks).
I want to return the names with unchanged status for the past weeks. Do you have any idea how to build it in dax? Really appreciate your help.

Data:

NAME          Week1       Week2      Week3       Week4        Week5        Week6
John             A                  B              C                C                C                  C
Ray               B                  B              B                 B                B                  B
Mary            A                  B               C                 D               E                  F

GOAL:
Name        STATUS       COUNT
John            C                 4
Ray              B                 6
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    3 years ago

    Hi jiji ，

    Pls refer the below:

    1.unpovit:

    2.use the below dax to create new column:

    WEEK2 = RIGHT('Table 2'[Attribute],1)
    COUNT = CALCULATE(COUNT('Table 2'[Value]),FILTER(ALL('Table 2'),'Table 2'[Name]=EARLIER('Table 2'[Name])&&'Table 2'[Value]=EARLIER('Table 2'[Value])))
    COUNT2 = 'Table 2'[COUNT]+0.1*'Table 2'[WEEK2]
    TEST = MAXX(FILTER('Table 2','Table 2'[Name]=EARLIER('Table 2'[Name])),'Table 2'[COUNT]+0.1*'Table 2'[WEEK2])

     

     

    3.create new table:

    Table 3 = SELECTCOLUMNS(FILTER('Table 2','Table 2'[COUNT2]='Table 2'[TEST]),"Name", 'Table 2'[Name],"Status",'Table 2'[Value],"count",'Table 2'[COUNT])

    Output result:

     

     

    Best Regards

    Lucien

3 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    3 years ago

    Hi jiji ，

    Pls refer the below:

    1.unpovit:

    2.use the below dax to create new column:

    WEEK2 = RIGHT('Table 2'[Attribute],1)
    COUNT = CALCULATE(COUNT('Table 2'[Value]),FILTER(ALL('Table 2'),'Table 2'[Name]=EARLIER('Table 2'[Name])&&'Table 2'[Value]=EARLIER('Table 2'[Value])))
    COUNT2 = 'Table 2'[COUNT]+0.1*'Table 2'[WEEK2]
    TEST = MAXX(FILTER('Table 2','Table 2'[Name]=EARLIER('Table 2'[Name])),'Table 2'[COUNT]+0.1*'Table 2'[WEEK2])

     

     

    3.create new table:

    Table 3 = SELECTCOLUMNS(FILTER('Table 2','Table 2'[COUNT2]='Table 2'[TEST]),"Name", 'Table 2'[Name],"Status",'Table 2'[Value],"count",'Table 2'[COUNT])

    Output result:

     

     

    Best Regards

    Lucien

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    For the row of Mary, if there were 3 C's and 3 F's, then what result would you expect?

    • jiji's avatar
      jiji
      Frequent Visitor
      3 years ago

      Hello Ashish, Thank you for your message. In that scenario, I would only be interested on the latest dates unchanged status. Say:
      NAME   W1   W2   W3    W4   W5   W6
      MAR     C       C      A      A       F      F

      GOAL IS 
      NAME      STATUS       COUNT
      MARY         F                  2

      I hope this makes sense. My data in the back end is the typical: Name, Date, Status Columns.
      Then in the dashboard I just created a matrix table so that I could place Date as columns then count the number of status.