If statement based on the count

Saxon202202 

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! 🤠