Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have a following sample data table:
Here is it in Table form:
Prod_Code | Fist_Sales_Date | Analog_Group | Importance | Group_Prod_Code |
Code_1 | 01-02-18 | 1 | Code_2 | |
Code_2 | 15-03-17 | 1 | Spectrum | Code_2 |
Code_3 | 02-08-17 | 2 | Code_7 | |
Code_4 | 01-02-22 | 3 | Code_4 | |
Code_5 | 10-10-21 | 1 | Code_2 | |
Code_6 | 12-11-20 | Code_6 | ||
Code_7 | 30-05-16 | 2 | Spectrum | Code_7 |
Code_8 | 20-05-18 | Code_8 | ||
Code_9 | 20-11-22 | Code_9 | ||
Code_10 | 20-05-18 | 3 | Code_4 |
The goal is the get grouped the codes in the last column "Group_Prod_Code" based on following criteria:
1. Column "Analog_Group" represents groups among the set of product codes. Some of products may have "Analog Groups", but some may not
2. Within the Analog groups, there are some "Leading" products, called "Spectrum" (Column "Importance)
3. So, here are the final desired results to get:
3.1. If "Analog Group" has "Spectrum", then The "Group_Prod_Code" should be the one, which is generated from the "Spectrum product" within the group - This is the first criteria ((Example: Group#1 and 2)
3.2. If there is no "Spectrum", then the "Leading" product and respectively the "Group_Prod_Code" should be the product, with the latest "Sales Date". Column "First Sales Date" (Example: Group#3)
3.3. If there is no Analog_Group, then the End result for "Group_Product_Code" should be the same as the original product code
I Have managed to generate a "Calculated Column" grouping the analog_groups with only "Latest Sales Date", however I can not add the first criteria with "Spectrum" 😞
Analog_Leader_Code =
VAR Vol=C_GOODS[ANALOG_GROUP]
VAR Miz=C_GOODS[MIZNIAN]
VAR MX=CALCULATE(max(C_GOODS[First Prod Sales Date]),FILTER(C_GOODS,C_GOODS[ANALOG_GROUP]=Vol))
Return
If(Vol=BLANK(),C_GOODS[Prod_Code],
//if(Miz="Spectrum", IFERROR(LOOKUPVALUE(C_GOODS[პროდუქციის კოდი],C_GOODS[ANALOG_PROD_GROUP],Vol,C_GOODS[MIZNIAN],"Spectrum"),BLANK()),
IFERROR(LOOKUPVALUE(C_GOODS[Prod_Code],C_GOODS[ANALOG_GROUP],Vol,C_GOODS[First Prod Sales Date],MX),BLANK())
)
Thanks in advance,
Solved! Go to Solution.
hi @George1973
try to add a column like:
Group_Prod_Code2 =
VAR _grouptable =
FILTER(
GOODS,
GOODS[Analog_Group] = EARLIER(GOODS[Analog_Group])
)
VAR _spectable =
FILTER( _grouptable, GOODS[Importance] = "Spectrum" )
VAR _condition1 =
COUNTROWS(_spectable)=1
VAR _speccode =
MINX( _spectable, GOODS[Prod_Code])
VAR _lastsalesdate =
MAXX(_grouptable, GOODS[Fist_Sales_Date])
VAR _lastsalescode =
MAXX( FILTER(_grouptable, GOODS[Fist_Sales_Date] =_lastsalesdate), GOODS[Prod_Code] )
RETURN
SWITCH(
TRUE(),
_condition1, _speccode,
[Analog_Group]<>BLANK(), _lastsalescode,
[Prod_Code]
)
it worked like:
Analog_Leader_Code =
VAR Vol=C_GOODS[ANALOG_GROUP]
RETURN
IF(Vol="",C_GOODS[Prod_Code],COALESCE(MAXX(FILTER(C_GOODS,C_GOODS[ANALOG_GROUP]=Vol&&C_GOODS[MIZNIAN]="Spectrum"),C_GOODS[Prod_Code]),MAXX(TOPN(1,FILTER(C_GOODS,C_GOODS[ANALOG_GROUP]=Vol),C_GOODS[First Prod Sales Date]),C_GOODS[Prod_Code])))
hi @George1973
try to add a column like:
Group_Prod_Code2 =
VAR _grouptable =
FILTER(
GOODS,
GOODS[Analog_Group] = EARLIER(GOODS[Analog_Group])
)
VAR _spectable =
FILTER( _grouptable, GOODS[Importance] = "Spectrum" )
VAR _condition1 =
COUNTROWS(_spectable)=1
VAR _speccode =
MINX( _spectable, GOODS[Prod_Code])
VAR _lastsalesdate =
MAXX(_grouptable, GOODS[Fist_Sales_Date])
VAR _lastsalescode =
MAXX( FILTER(_grouptable, GOODS[Fist_Sales_Date] =_lastsalesdate), GOODS[Prod_Code] )
RETURN
SWITCH(
TRUE(),
_condition1, _speccode,
[Analog_Group]<>BLANK(), _lastsalescode,
[Prod_Code]
)
it worked like:
Thanks a lot! Yes, it works now.
Great! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |