Count Promos which have changed position

[Anonymous]

Hi all. I have this sample data (my actual data is much larger and complex):

promo	position
a	A1
a	B1
a	C1
b	A1
b	A1
b	B1
c	B1
c	B1
c	B1
d	C1
d	C1
d	C1
e	P1
e	P1
e	P1

I want to detect how many unique "promos" have had a changed in "position"? Looking at the table, I can identify that 2 promos have changed position only i.e. promo a and promo b. How can I make a measure that will show me this exact number, without having to count each time because the actual data is big?

Thanks in advance 

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I am not sure how your actual data model looks like, but try the below.

Or share your sample pbix file's link here, then a more accurate measure can be written.

Count unique promo that changed in position =

VAR newtable =

FILTER (

SUMMARIZE (

'Table',

'Table'[promo],

'Table'[position],

"@countrow", COUNTROWS ( 'Table' )

),

[@countrow] = 1

)

RETURN

COUNTROWS ( SUMMARIZE ( newtable, 'Table'[promo] ) )+0

 https://www.dropbox.com/s/7qh1q46s1yhqgol/etv2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

You can easily create a measure to show results.

Like this:

Measure =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[promo],
        "a", IF ( DISTINCTCOUNT ( 'Table'[position] ) <> 1, 1, 0 )
    ),
    [a]
)

If you still have problems, please feel free to ask me.

Best Regards

Janey Guo

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

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I am not sure how your actual data model looks like, but try the below.

Or share your sample pbix file's link here, then a more accurate measure can be written.

Count unique promo that changed in position =

VAR newtable =

FILTER (

SUMMARIZE (

'Table',

'Table'[promo],

'Table'[position],

"@countrow", COUNTROWS ( 'Table' )

),

[@countrow] = 1

)

RETURN

COUNTROWS ( SUMMARIZE ( newtable, 'Table'[promo] ) )+0

 https://www.dropbox.com/s/7qh1q46s1yhqgol/etv2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM