cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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 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
1 ACCEPTED SOLUTION
Community Support

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 6
Community Support

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
Not applicable

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

Community Support

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
Not applicable

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

Super User

Do a distinct count of categories per user and subtract 1

Super User

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors