The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi,
Item | Mode | UK Pallet | US Pallet | EURO Pallet | Status |
123 | Train | 350 | 450 | 600 | Same Pallet Size For Both Mode |
123 | Air | 350 | 450 | 600 | Same Pallet Size For Both Mode |
124 | Train | 150 | 150 | 150 | Unique Pallet Size For Both Mode |
124 | Air | 350 | 450 | 600 | Unique Pallet Size For Both Mode |
125 | Air | 89 | 89 | 89 | Different Pallet Size Within The Mode |
125 | Air | 45 | 78 | 46 | Different Pallet Size Within The Mode |
125 | Air | 231 | 548 | 650 | Different Pallet Size Within The Mode |
125 | Air | 890 | 790 | 250 | Different Pallet Size Within The Mode |
126 | Train | 650 | 450 | 360 | Different Pallet Size Within The Mode |
126 | Train | 250 | 380 | 650 | Different Pallet Size Within The Mode |
126 | Train | 150 | 250 | 350 | Different Pallet Size Within The Mode |
126 | Train | 100 | 300 | 600 | Different Pallet Size Within The Mode |
127 | Air | 236 | 458 | 547 | Different Pallet Size For Both Mode |
127 | Air | 233 | 158 | 460 | Different Pallet Size For Both Mode |
127 | Air | 231 | 548 | 650 | Different Pallet Size For Both Mode |
127 | Air | 890 | 790 | 250 | Different Pallet Size For Both Mode |
127 | Train | 150 | 250 | 350 | Different Pallet Size For Both Mode |
127 | Train | 100 | 300 | 600 | Different Pallet Size For Both Mode |
127 | Train | 225 | 355 | 555 | Different Pallet Size For Both Mode |
128 | Air | 654 | 258 | 478 | Single Pallet Size For Sigle Mode |
129 | Train | 765 | 457 | 657 | Single Pallet Size For Sigle Mode |
130 | Train | 12 | 12 | 12 | Single Pallet Size For Sigle Mode |
131 | Air | 18 | 17 | 79 | Single Pallet Size For Sigle Mode |
Condition
1. If the same item has different mode and where the count equal to 1 based on the item & mode and UK, US & EURO sizes are same then return -“Same Pallet Size For Both Mode”.
2 .If the same item has different mode and where the count equal to 1 based on the item & mode and UK, US & EURO sizes are different then return - “Unique Pallet Size For Both Mode”.
3.If the same item has same mode and where the count greater than 1 based on the item & mode and UK, US & EURO sizes are different then return - “Different Pallet Size Within The Mode”.
4.If the same item has different mode and where the count greater than 1 based on the item & mode and UK, US & EURO sizes are different then return - “Different Pallet Size Within The Mode”.
5.If item and mode have unique count, then return “Single Pallet Size For Sigle Mode”.
I am looking for DAX code to get the result.
Solved! Go to Solution.
output :
DAX :
calculated column =
var itm = Table_4[Item]
var uk_pallet = Table_4[UK Pallet]
var us_pallet = Table_4[US Pallet]
var euro_pallet = Table_4[EURO Pallet]
var ds =
ADDCOLUMNS(
filter(
ALLNOBLANKROW(Table_4),
Table_4[Item] = itm
),
"@X", CALCULATE(DISTINCTCOUNT(Table_4[Mode]),ALLEXCEPT(Table_4,Table_4[Item])), -- check if we have more than 1 mode per item
"@C" , CALCULATE(COUNT(Table_4[Mode]), ALLEXCEPT(Table_4,Table_4[Item],Table_4[Mode])) -- check nb item-mode existence
)
var suk = SUMX(ds,Table_4[UK Pallet])
var sus = SUMX(ds,Table_4[US Pallet])
var seuro = SUMX(ds,Table_4[EURO Pallet])
var suk_same = suk/countrows(ds) = uk_pallet
var sus_same = sus/countrows(ds) = us_pallet
var seuro_same = seuro/countrows(ds) = euro_pallet
var C=
MAXX(
ds,
[@C]
)
--check if has different mode
-- if check returns 0 , then we hav only 1 mode per this item.
var X =
MAXX(
ds
,[@X])
var res =
SWITCH(
TRUE(),
X > 1 && C = 1 && suk_same = TRUE() && sus_same = TRUE() && seuro_same = TRUE() , "Same Pallet Size For Both Mode",
X > 1 && C = 1 && (suk_same = False() || sus_same = False() || seuro_same = False()) , "Unique Pallet Size For Both Mode",
X = 1 && C > 1 && (suk_same = False() || sus_same = False() || seuro_same = False()) , "Different Pallet Size Within The Mode",
X > 1 && C > 1 && (suk_same = False() || sus_same = False() || seuro_same = False()) , "Different Pallet Size For Both Mode",
x=1 && c = 1 ,"Single Pallet Size For Sigle Mode"
)
return res -- CONCATENATEX(ds,[@X] & "-" & [@C] ,",")
replace the table name with your table name and the coluymn names with your column names .
let me know if it works for you .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
output :
DAX :
calculated column =
var itm = Table_4[Item]
var uk_pallet = Table_4[UK Pallet]
var us_pallet = Table_4[US Pallet]
var euro_pallet = Table_4[EURO Pallet]
var ds =
ADDCOLUMNS(
filter(
ALLNOBLANKROW(Table_4),
Table_4[Item] = itm
),
"@X", CALCULATE(DISTINCTCOUNT(Table_4[Mode]),ALLEXCEPT(Table_4,Table_4[Item])), -- check if we have more than 1 mode per item
"@C" , CALCULATE(COUNT(Table_4[Mode]), ALLEXCEPT(Table_4,Table_4[Item],Table_4[Mode])) -- check nb item-mode existence
)
var suk = SUMX(ds,Table_4[UK Pallet])
var sus = SUMX(ds,Table_4[US Pallet])
var seuro = SUMX(ds,Table_4[EURO Pallet])
var suk_same = suk/countrows(ds) = uk_pallet
var sus_same = sus/countrows(ds) = us_pallet
var seuro_same = seuro/countrows(ds) = euro_pallet
var C=
MAXX(
ds,
[@C]
)
--check if has different mode
-- if check returns 0 , then we hav only 1 mode per this item.
var X =
MAXX(
ds
,[@X])
var res =
SWITCH(
TRUE(),
X > 1 && C = 1 && suk_same = TRUE() && sus_same = TRUE() && seuro_same = TRUE() , "Same Pallet Size For Both Mode",
X > 1 && C = 1 && (suk_same = False() || sus_same = False() || seuro_same = False()) , "Unique Pallet Size For Both Mode",
X = 1 && C > 1 && (suk_same = False() || sus_same = False() || seuro_same = False()) , "Different Pallet Size Within The Mode",
X > 1 && C > 1 && (suk_same = False() || sus_same = False() || seuro_same = False()) , "Different Pallet Size For Both Mode",
x=1 && c = 1 ,"Single Pallet Size For Sigle Mode"
)
return res -- CONCATENATEX(ds,[@X] & "-" & [@C] ,",")
replace the table name with your table name and the coluymn names with your column names .
let me know if it works for you .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠