If statement based on the count

[Saxon202202]

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.

[Daniel29195 · Accepted solution]

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

[Saxon202202]

@Daniel29195 , Thanks for your support and help. It's working fine.

[Daniel29195]

@Saxon202202 

where the count = 1  ?  count of what  ? 

[Saxon202202]

@Daniel29195 count for item and mode