DAX statement remove duplicate values

[privledged_test]

Is there a way to ignore duplicate values from source table using DAX? In below data "_DocTotalMisc" column is bringing same value for three order line but actual order form only has value in line 1 of the order. If there a DAX function where I can say if for particular Order#, line 1 has value <>0 then force line 2 and after to state "$0"?

[DataNinja777 · Accepted solution]

Hi @privledged_test ,

To show the value.OrderHed_DocTotalMisc only on the first line of each order and return 0 for all subsequent lines, you can write a DAX measure that identifies the first line number for each order and compares it with the current line in context. If it's the first line, return the value; otherwise, return 0. Here's the DAX:

DocTotalMisc_OneLineOnly =
VAR CurrentLine = SELECTEDVALUE('YourTable'[Line#])
VAR FirstLine =
    CALCULATE(
        MIN('YourTable'[Line#]),
        ALLEXCEPT('YourTable', 'YourTable'[Order#])
    )
RETURN
    IF(CurrentLine = FirstLine, MAX('YourTable'[value.OrderHed_DocTotalMisc]), 0)

Replace 'YourTable' with the actual table name. This will ensure the DocTotalMisc is only shown once per order, typically on the first line, and all other lines will display 0. This should be written as a measure to ensure proper behavior within visuals that filter by order and line.

Best regards,