Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
LieneB
New Member

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

 

1 ACCEPTED SOLUTION
eliasayyy
Super User
Super User

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

 

 

annonymous1999_0-1693399933541.png

 



View solution in original post

2 REPLIES 2
eliasayyy
Super User
Super User

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

 

 

annonymous1999_0-1693399933541.png

 



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")

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors