Calculate Firstnonblank based on the priority DAX

Saxon202202 

output

uk pallet ( result ) = 
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 air_data = 
SELECTCOLUMNS(
FILTER(
    ALLNOBLANKROW(Table_4),
    Table_4[Item] = itm && Table_4[Mode] = "Air" ),
    Table_4[UK Pallet]
)

var res = 
SWITCH(
    TRUE(),
    X > 1 && C = 1 &&  suk_same = TRUE()  , "" &suk / countrows(ds)& "" ,
    X > 1 && C = 1 &&   suk_same = False()  , "" & air_data & "",
    X = 1 && C > 1 &&   suk_same = False()  , "X",
    X > 1  && suk_same = False()  , "X",
    x>1 && c > 1 && suk_same = TRUE() ,"" & air_data & ""

   

)

return res -- CONCATENATEX(ds,[@X] & "-" & [@C] ,",")

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

Hy,

I was able to get the desired result using this calculated dax column

UK Pallet Result = 
VAR currentItem = 'RESULT'[Item]
VAR uniqueValues = 
    CALCULATETABLE(
        DISTINCT('DATA'[UK Pallet]), 
        ALL('DATA'), 
        'DATA'[Item] = currentItem
    )
VAR countOfUniqueValues = COUNTROWS(uniqueValues)
VAR airValue = 
    CALCULATE(
        MIN('DATA'[UK Pallet]), 
        ALL('DATA'),
        'DATA'[Item] = currentItem, 
        'DATA'[Mode] = "Air"
    )
VAR trainValue = 
    CALCULATE(
        MIN('DATA'[UK Pallet]), 
        ALL('DATA'),
        'DATA'[Item] = currentItem, 
        'DATA'[Mode] = "Train"
    )
VAR airValueCount = 
    CALCULATE(
        COUNTROWS('DATA'), 
        ALL('DATA'),
        'DATA'[Item] = currentItem, 
        'DATA'[Mode] = "Air"
    )
VAR trainValueCount = 
    CALCULATE(
        COUNTROWS('DATA'), 
        ALL('DATA'),
        'DATA'[Item] = currentItem, 
        'DATA'[Mode] = "Train"
    )
VAR resultValue = 
    IF(
        countOfUniqueValues = 1, 
        FORMAT(MINX(uniqueValues, [UK Pallet]), "General Number"),
        IF(
            countOfUniqueValues > 1 && (airValueCount = 1 || trainValueCount = 1),
            IF(
                airValueCount = 1, 
                FORMAT(airValue, "General Number"), 
                IF(
                    trainValueCount = 1, 
                    FORMAT(trainValue, "General Number"), 
                    "X"
                )
            ),
            "X"
        )
    )
RETURN
    resultValue

PBIX file link: Calculate Firstnonblanks with priprity-0902.pbix

Here is the output: