count category changes per user

[Anonymous]

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	category	changes	n changes
9100	11/07/2019	05/07/2019	category 1
9100	11/10/2019	11/10/2019	category 2	x	1
9100	02/12/2019	29/11/2019	category 2
2222	02/11/1982	30/09/1990	category 1	x
2222	01/10/1990	30/09/1993	category 2	x
2222	01/10/1993	28/02/1994	category 3	x	3
3698	01/03/2004	31/12/2005	category 5
3698	01/01/2006	31/12/9999	category 5		0

[v-luwang-msft · Accepted solution]

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]

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]

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]

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

[lbendlin]

Do a distinct count of categories per user and subtract 1

[lbendlin]

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?