Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

count category changes per user

Hello
How can i count how many times the user changes category?
How to calculate the value of n changes?
ps the changes column doesn't exist in my table
thanks

 

idstart dateend datecategorychangesn changes
910011/07/201905/07/2019category 1  
910011/10/201911/10/2019category 2x1
910002/12/201929/11/2019category 2  
222202/11/198230/09/1990category 1x 
222201/10/199030/09/1993category 2x 
222201/10/199328/02/1994category 3x3
369801/03/200431/12/2005category 5  
369801/01/200631/12/9999category 5 0
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    3 years ago

    Hi Anonymous ，

    Pls use the below dax to create a new column:

    n change1 =
VAR maxdate =
    CALCULATE ( MAX ( 'Table'[start date] ), ALLEXCEPT ( 'Table', 'Table'[id] ) )
RETURN
    IF (
        'Table'[start date] = maxdate,
        IF (
            CALCULATE (
                COUNT ( 'Table'[id] ),
                ALLEXCEPT ( 'Table', 'Table'[id] ),
                'Table'[changes] = "x"
            )
                = BLANK (),
            0,
            CALCULATE (
                COUNT ( 'Table'[id] ),
                ALLEXCEPT ( 'Table', 'Table'[id] ),
                'Table'[changes] = "x"
            )
        ),
        BLANK ()
    )

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,
    Lucien

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    User 2222 only changed category twice, not three times.

     

    Do you want to report on any changes or only on changes to a new category?

     

    Let's say the third line of 9100 would be category 1.  Does this now count as two changes?

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

    Hi Anonymous ，

    Pls use the below dax to create a new column:

    n change1 =
VAR maxdate =
    CALCULATE ( MAX ( 'Table'[start date] ), ALLEXCEPT ( 'Table', 'Table'[id] ) )
RETURN
    IF (
        'Table'[start date] = maxdate,
        IF (
            CALCULATE (
                COUNT ( 'Table'[id] ),
                ALLEXCEPT ( 'Table', 'Table'[id] ),
                'Table'[changes] = "x"
            )
                = BLANK (),
            0,
            CALCULATE (
                COUNT ( 'Table'[id] ),
                ALLEXCEPT ( 'Table', 'Table'[id] ),
                'Table'[changes] = "x"
            )
        ),
        BLANK ()
    )

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,
    Lucien

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks for your response v-luwang-msft .
      but the changes column doesn't exist in my data model. how can i create it?

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

        Hi Anonymous ，

        Use the following dax to create a new column:

        change1 = 
VAR rank1 =
    RANKX (
        FILTER ( ALL ( 'Table' ), 'Table'[id] = EARLIER ( 'Table'[id] ) ),
        'Table'[end date],
        ,
        ASC,
        DENSE
    )
RETURN
    IF (
        rank1 <> 1
            && CALCULATE (
                MAX ( 'Table'[category] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[id] = EARLIER ( 'Table'[id] )
                        && RANKX (
                            FILTER ( ALL ( 'Table' ), 'Table'[id] = EARLIER ( 'Table'[id] ) ),
                            'Table'[end date],
                            ,
                            ASC,
                            DENSE
                        ) = rank1 - 1
                )
            ) <> 'Table'[category],
        "x",
        BLANK ()
    )

         

        Output:(And I think the first id=2222 not need "x" )

         

         

        Best Regards

        Lucien

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello,
    Thanks for your response lbendlin .
    only changes of a new category are important.
    any idea how to do this?

    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago

      Do a distinct count of categories per user and subtract 1