Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count Promos which have changed position

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

promoposition
aA1
aB1
aC1
bA1
bA1
bB1
cB1
cB1
cB1
dC1
dC1
dC1
eP1
eP1
eP1

 

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 

Need Help
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    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
     

    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's avatar
    v-janeyg-msft
    5 years ago

    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.

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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
     

    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's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    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.