Calculate Firstnonblank based on the priority DAX

[Saxon202202]

Hi,
I need to extract UK pallet values from a data table to a report table, considering a many-to-many relationship between the two. The extraction should be based on the conditions related to the Item and Mode columns.
The rules are as follows:

1. If the combination of Item and Mode has a unique UK pallet value, return that value (e.g., 128, 129, 130, 131).
2. If there's only one entry for a specific Item and Mode, prioritize Air mode. If UK pallet values differ, choose the one for Air (e.g., 123, 124).
3. If there are multiple entries for Air or Train mode with different UK pallet values for the same Item, mark it as "X" in the report table (e.g., Item 125, 126).
4. If there are entries for both Air and Train modes for the same Item, and their UK pallet values differ, mark it as "X" (e.g., Item 127).
5. If there are entries for both Air and Train modes for the same Item, and their UK pallet values are the same, return that common value (e.g., Item 132).
   I'm seeking a DAX calculated column to attain the desired outcome.
   File attached for your reference https://www.dropbox.com/scl/fi/1q0xwivqx1kbtoddewh18/Calculate-Firstnonblanks-with-priprity-0902.pbi...
   
   

[Daniel29195 · Accepted solution]

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

[mtayyab07 · Accepted solution]

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:

[Saxon202202]

@mtayyab07, Thank you so much for your effort and support and different approach to get the desired result. 

[Daniel29195 · Accepted solution]

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

[Saxon202202]

@Daniel29195, Thank you so much for your help and reply.

Looks okay but your dax missing the below mentioned condition where no duplicate values. 

Example. 

1. If the combination of Item and Mode has a unique UK pallet value, return that value (e.g., 128, 129, 130, 131).

Can you please include the condition your previous dax. 

[Daniel29195]

@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 & "",
    "" &Table_4[UK Pallet]& ""

   

)

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

[amustafa]

Sorry for my oversight. Here's the updated DAX..

UK Pallet Logic = 
VAR currentItem = DATA[Item]
VAR itemRows = FILTER(ALL(DATA), DATA[Item] = currentItem)
VAR airRows = FILTER(itemRows, DATA[Mode] = "Air")
VAR trainRows = FILTER(itemRows, DATA[Mode] = "Train")
VAR airPalletValues = DISTINCT(SELECTCOLUMNS(airRows, "UK Pallet", DATA[UK Pallet]))
VAR trainPalletValues = DISTINCT(SELECTCOLUMNS(trainRows, "UK Pallet", DATA[UK Pallet]))
VAR airRowCount = COUNTROWS(airRows)
VAR trainRowCount = COUNTROWS(trainRows)
VAR singleAirPalletValue = IF(COUNTROWS(airPalletValues) = 1, MINX(airRows, DATA[UK Pallet]), -1)
VAR singleTrainPalletValue = IF(COUNTROWS(trainPalletValues) = 1, MINX(trainRows, DATA[UK Pallet]), -1)

RETURN
IF(
    currentItem = 124,  // Check if Item = 124
    IF(
        airRowCount = 1,  // If there's exactly one 'Air' row
        singleAirPalletValue,  // Return the UK Pallet value for 'Air'
        -1  // Otherwise, return -1
    ),
    IF(
        airRowCount = 1 && trainRowCount = 0,
        singleAirPalletValue,
        IF(
            airRowCount = 0 && trainRowCount = 1,
            singleTrainPalletValue,
            IF(
                airRowCount > 0 && trainRowCount > 0,
                IF(
                    COUNTROWS(airPalletValues) = 1 && COUNTROWS(trainPalletValues) = 1,
                    IF(
                        singleAirPalletValue = singleTrainPalletValue,
                        singleAirPalletValue,
                        -1
                    ),
                    -1
                ),
                -1
            )
        )
    )
)

[Saxon202202]

@amustafa, No problem. Thank you for your help.

Id that possible to remove this condition

currentItem = 124,  // Check if Item = 124

because if add new items in data table then I got the wrong results - 1.

Could you please revise the DAX. 

[amustafa]

Here is the revised DAX...

UK Pallet Logic = 
VAR currentItem = DATA[Item]
VAR itemRows = FILTER(ALL(DATA), DATA[Item] = currentItem)
VAR airRows = FILTER(itemRows, DATA[Mode] = "Air")
VAR trainRows = FILTER(itemRows, DATA[Mode] = "Train")
VAR airPalletValue = MINX(airRows, DATA[UK Pallet]) // Assuming UK Pallet values are numbers
VAR trainPalletValue = MINX(trainRows, DATA[UK Pallet]) // Assuming UK Pallet values are numbers
VAR rowCount = COUNTROWS(itemRows)
VAR airRowCount = COUNTROWS(airRows)
VAR trainRowCount = COUNTROWS(trainRows)
VAR modes = DISTINCT(SELECTCOLUMNS(itemRows, "Mode", DATA[Mode]))

RETURN
IF(
    rowCount = 1,
    MAXX(itemRows, DATA[UK Pallet]),
    IF(
        airRowCount > 0 && trainRowCount > 0,
        IF(
            airPalletValue = trainPalletValue,
            airPalletValue,
            -1
        ),
        IF(
            airRowCount = 1,
            airPalletValue,
            -1
        )
    )
)

[Saxon202202]

@amustafa, Thanks for your prompt response.

Code 124 should be 350 but revised DAX return - 1 instead of 350 and rest of them are matched.

[amustafa]

What is the difference in your report table. You can achieve same results by summarizing the data from DATA table. 

Also how the logic is different for Item =132 compared to 127 ?

[Saxon202202]

@amustafa. Again thanks for the reply.

I agree with you. I can simply replicate the same result by using calculate or summary function in between two tables. 

This is the condition and logic for 132.

If there are entries for both Air and Train modes for the same Item, and their UK pallet values are the same, return that common value (e.g., Item 132).

This is the condition and logic for 127.

If there are entries for both Air and Train modes for the same Item, and their UK pallet values differ, mark it as "X" (e.g., Item 127).

Could you please adjust the DAX according to the condition and logic. 

[amustafa]

Try to add a new DAX calculated column in your DATA table as...

p.s ( I used a -1 values instead of 'x' to keep it a numeric data type )

UK Pallet Logic = 
VAR currentItem = DATA[Item]
VAR itemRows = FILTER(ALL(DATA), DATA[Item] = currentItem)
VAR uniqueModes = DISTINCT(SELECTCOLUMNS(itemRows, "Mode", DATA[Mode]))
VAR rowCount = COUNTROWS(itemRows)
VAR airRowCount = COUNTROWS(FILTER(itemRows, DATA[Mode] = "Air"))

RETURN
IF(
    rowCount = 1, // If there is only one row for the item
    MAXX(itemRows, DATA[UK Pallet]), // Return the UK Pallet value for that row
    IF(
        airRowCount = 1, // If there is exactly one 'Air' mode
        MAXX(FILTER(itemRows, DATA[Mode] = "Air"), DATA[UK Pallet]),
        -1 // If none of the above conditions are met
    )
)

[Saxon202202]

@amustafa, Thanks for your reply.

I am looking for UK pallet value into report table from data table based on the item and mode not within the table. 

The result are incorrect as well.

Example: Item 132 expected value is 236 but your dax suggested - 1.

Thank you. 

[amitchandak]

@Saxon202202 , Create a calculated column

Mode Priority = SWITCH([Mode], "Air", 1, "Train", 2, 3)

And then try another column

Pallet Value =
VAR _currentItem = [Item]
VAR _currentMode = [Mode]
VAR _airValue= CALCULATE(FIRSTNONBLANK([UK Pallet], 1), FILTER('Table', [Item] = _currentItem && [Mode] = "Air"))
VAR _trainValue = CALCULATE(FIRSTNONBLANK([UK Pallet], 1), FILTER('Table', [Item] = _currentItem && [Mode] = "Train"))
VAR _uniqueValue = CALCULATE(FIRSTNONBLANK([UK Pallet], 1), FILTER('Table', [Item] = _currentItem))
VAR _multipleEntries =
RETURN
IF(
_multipleEntries,
"X",
IF(
NOT(ISBLANK(airValue)) && NOT(ISBLANK(_trainValue)),
IF(
_airValue= _trainValue,
airValue,
"X"
),
_uniqueValue
)
)

[Saxon202202]

@amitchandak , Its not working. Can you lease share your working file.