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 ...
  • 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()
)

     

     

     



eliasayyy's avatar
eliasayyy
Icon for Memorable Member rankMemorable 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")