Forum Discussion

Saxon202202's avatar
Saxon202202
Helper III
2 years ago
Solved

Calculate Firstnonblank based on the priority DAX

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.pbix?rlkey=3zn4vne9zt466duorxq8931zz&dl=0

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    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's avatar
    mtayyab07
    2 years ago

    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:

15 Replies

  • mtayyab07's avatar
    mtayyab07
    Frequent Visitor
    2 years ago

    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's avatar
      Saxon202202
      Helper III
      2 years ago

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

  • amustafa's avatar
    amustafa
    Solution Sage
    2 years ago

    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's avatar
      Saxon202202
      Helper III
      2 years ago

      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. 

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    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's avatar
      Saxon202202
      Helper III
      2 years ago

      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's avatar
        Daniel29195
        Community Champion
        2 years ago

        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] ,",")

         

         

         

         

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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
    )
    )

  • amustafa's avatar
    amustafa
    Solution Sage
    2 years ago

    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's avatar
      Saxon202202
      Helper III
      2 years ago

      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. 

       

       

  • amustafa's avatar
    amustafa
    Solution Sage
    2 years ago

    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's avatar
      Saxon202202
      Helper III
      2 years ago

      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's avatar
    amustafa
    Solution Sage
    2 years ago

    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's avatar
      Saxon202202
      Helper III
      2 years ago

      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.