Forum Discussion

Saxon202202's avatar
Saxon202202
Icon for Helper III rankHelper 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 I...
Need Help
amustafa's avatar
amustafa
Icon for Solution Sage rankSolution 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
Icon for Helper III rankHelper 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.