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   id start date end date ca...
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

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