Hello Community,
I´m looking for a solution to find out how many EAN codes would be sold extra with an new purchase order type (P02). The regular one would be in this example P01.
So I tried to compare to dataconnections with excluding in 1 query P02 and compare it with the other which has all purchase order types. In this case it would works until the month august 2020 because then the customer has orderd the ean 00000000001 also over P01 and in my merge it will be lost. So I need to add the date dimension. If the EAN code was first orderd with P02 than it should count as new on top SKU and also sum up the turnover.
I think we speak about 2.000.000 millions rows with about 10.000-15.000 customers and about 500 EANs codes.
Would there be a smart solution to build up my target table which only shows the rows which are first time orderd with P02?
I hope it is clear for you 🙂
Thx
Both options work but I was using "PO2" with a capital O instead of P02, with a zero, See it all at work in the attached file.
Target =
FILTER (
Original,
Original[Purchase Order Type] = "P02"
&&
VAR firstDate_ =
CALCULATE (
MIN ( Original[Date] ),
ALLEXCEPT ( Original, Original[Customer Code], Original[EAN Code] )
)
VAR firstType_ =
CALCULATE (
DISTINCT ( Original[Purchase Order Type] ),
ALLEXCEPT ( Original, Original[Customer Code], Original[EAN Code] ),
Original[Date] = firstDate_
)
RETURN
firstType_ = "P02"
)
Target V2 =
FILTER (
Original,
Original[Purchase Order Type] = "P02"
&& "P02"
= MAXX (
TOPN (
1,
CALCULATETABLE (
Original,
ALLEXCEPT ( Original, Original[Customer Code], Original[EAN Code] )
),
Original[Date], ASC
),
Original[Purchase Order Type]
)
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Both options work but I was using "PO2" with a capital O instead of P02, with a zero, See it all at work in the attached file.
Target =
FILTER (
Original,
Original[Purchase Order Type] = "P02"
&&
VAR firstDate_ =
CALCULATE (
MIN ( Original[Date] ),
ALLEXCEPT ( Original, Original[Customer Code], Original[EAN Code] )
)
VAR firstType_ =
CALCULATE (
DISTINCT ( Original[Purchase Order Type] ),
ALLEXCEPT ( Original, Original[Customer Code], Original[EAN Code] ),
Original[Date] = firstDate_
)
RETURN
firstType_ = "P02"
)
Target V2 =
FILTER (
Original,
Original[Purchase Order Type] = "P02"
&& "P02"
= MAXX (
TOPN (
1,
CALCULATETABLE (
Original,
ALLEXCEPT ( Original, Original[Customer Code], Original[EAN Code] )
),
Original[Date], ASC
),
Original[Purchase Order Type]
)
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Another option:
Target =
FILTER (
Original,
Original[Purchase Order Type] = "PO2"
&& "PO2"
= MAXX (
TOPN (
1,
CALCULATETABLE (
Original,
ALLEXCEPT ( Original, Original[Customer Code], Original[EAN Code] )
),
Original[Date], ASC
),
Original[Purchase Order Type]
)
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Target =
FILTER (
Original,
Original[Purchase Order Type] = "PO2"
&&
VAR firstDate_ =
CALCULATE (
MIN ( Original[Date] ),
ALLEXCEPT ( Original, Original[Customer Code], Original[EAN Code] )
)
VAR firstType_ =
CALCULATE (
DISTINCT ( Original[Purchase Order Type] ),
ALLEXCEPT (
Original,
Original[Customer Code],
Original[EAN Code],
Original[Date] = firstDate_
)
)
RETURN
firstType_ = "PO2"
)
If this doesn't work, please paste the Original table in text-tabular format (instead of a screen cap) so that I can copy the contents and run a couple of tests
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi AIB,
I tried to build it up, but my table is empty:
Ok, you can just create a new calculated table:
Target =
FILTER (
Original,
Original[Purchase Order Type] = "PO2"
&&
VAR firstDate_ =
CALCULATE (
MIN ( Original[Date] ),
ALLEXCEPT (
Original,
Original[Customer Code],
Original[EAN Code],
Original[Purchase Order Type]
)
)
RETURN
Original[Date] = firstDate_
)Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @de_ppd
It's not completely clear. I'm not sure what the expected result is, a value (how many EAN codes would be sold extra) or a table (to build up my target table which only shows the rows which are first time orderd with P02)??
Please show some sample data and the expected result (and logic behind it) based on that sample data to help clarify.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hello AIB,
thank you for your feedback, I will try to explain it better:
So you can see that in the target table there are only values with "P02" and the lines from products who P02 was the first transaction date.
Best regards
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
