Forum Discussion

LieneB's avatar
LieneB
New Member
3 years ago
Solved

Flag Items based on condition

Hi!

Can anyone help me with creating measure to mark/flag items in the table in Power BI.

How to mark with "Additional sales" flag those "Sales ID nr" items that have following conditions in place -

same "Salesperson" sold different "Product" to the same "Client code", within 30 days period...

Data table is bellow , thank you in advance!

Sales ID nrSalespersonClient codeSales dateProduct
1HarA101.01.2023A
2HarA109.02.2023B
3HarA129.02.2023B
4HarA201.01.2023A
5HarA209.02.2023A
6HarA229.02.2023B
7HarA301.01.2023A
8HarA320.01.2023B
9HarA330.01.2023C
10HarA401.01.2023A
11HarA401.01.2023B
12HarA401.01.2023C
13HarA401.01.2023D
14HarA420.01.2023E
15EliA420.01.2023E
23EliA101.01.2023A
24EliA102.01.2023A
25EliA103.01.2023B
26EliA204.01.2023A
27EliA205.01.2023B
28EliA206.01.2023C
29EliA307.01.2023C
30EliA308.01.2023C
31EliA309.01.2023C
32EliA410.01.2023A
33EliA411.01.2023A
34EliA412.01.2023B
35EliA413.01.2023B
36EliA414.01.2023C
37VaiA415.01.2023C
45VaiA101.01.2023A
46VaiA202.01.2023B
47VaiA303.01.2023C
48VaiA404.01.2023D
49VaiA505.01.2023E
50VaiA606.01.2023F
51VaiA707.01.2023G
52VaiA708.01.2023G

 

  • eliasayyy's avatar
    eliasayyy
    3 years ago

    hello LieneB ,

    first you have an error in your data , ther eis no february 29 tin the year 2023 be careful 

    please try 

     

    Additional Sales Flag = 
VAR CurrentSalesID = 'Table'[Sales ID nr]
VAR CurrentSalesperson = 'Table'[Salesperson]
VAR CurrentClientCode = 'Table'[Client code]
VAR CurrentProduct = 'Table'[Product]
VAR CurrentSalesDate = 'Table'[Sales date]

RETURN 
IF(
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[Sales ID nr] <> CurrentSalesID &&
            'Table'[Salesperson] = CurrentSalesperson &&
            'Table'[Client code] = CurrentClientCode &&
            'Table'[Product] <> CurrentProduct &&
            DATEDIFF(CurrentSalesDate, 'Table'[Sales date], DAY) >= 0 &&
            DATEDIFF(CurrentSalesDate, 'Table'[Sales date], DAY) <= 30
        )
    ) > 0,
    "Additional sales",
    BLANK()
)

     

     

     



2 Replies

  • eliasayyy's avatar
    eliasayyy
    Memorable Member
    3 years ago

    hello LieneB ,

    first you have an error in your data , ther eis no february 29 tin the year 2023 be careful 

    please try 

     

    Additional Sales Flag = 
VAR CurrentSalesID = 'Table'[Sales ID nr]
VAR CurrentSalesperson = 'Table'[Salesperson]
VAR CurrentClientCode = 'Table'[Client code]
VAR CurrentProduct = 'Table'[Product]
VAR CurrentSalesDate = 'Table'[Sales date]

RETURN 
IF(
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[Sales ID nr] <> CurrentSalesID &&
            'Table'[Salesperson] = CurrentSalesperson &&
            'Table'[Client code] = CurrentClientCode &&
            'Table'[Product] <> CurrentProduct &&
            DATEDIFF(CurrentSalesDate, 'Table'[Sales date], DAY) >= 0 &&
            DATEDIFF(CurrentSalesDate, 'Table'[Sales date], DAY) <= 30
        )
    ) > 0,
    "Additional sales",
    BLANK()
)

     

     

     



    • LieneB's avatar
      LieneB
      New Member
      3 years ago

      Thank you so much! It worked for me. But with a minor change:

      .........DATEDIFF('Table'[Sale date],CurrentSalesDate, DAY) >= 0 &&
      DATEDIFF('Table'[Sale date],CurrentSalesDate, DAY) <= 30.......
      But for results to be completely precise, I need to eliminate 2 types of mistakes:
      1)If Inital sales and Aditional sales happen on the same date, then only one "Sales ID nr" (doesn't matter which one) has to have the flag (Example - Sales ID nr  "10"  and "11");
      2)If Adittional sales of same product happen more than once in the 30 day period, then only first "Sales ID nr" has to have the flag (Example - Sales ID nr  "36"  and "37")