Double row one result

[Cocrodile]

DTA	ID	VALUE
2024-07-01	12345	8
2024-07-01	12345	8
2024-07-01	12347	8

I need to create a measurement that makes me output Value= 8 for ID=12345 when the DTA is the same.

I Don't want sum but distinct 

[Anonymous · Accepted solution]

Hi @Cocrodile ,

I create a table as you mentioned.

Then I create a calculated column and here is the DAX code.

Column = 
VAR CurrentRowDTA = 'Table'[DTA]
VAR CurrentRowID = 'Table'[ID]
VAR CurrentRowVALUE = 'Table'[VALUE]
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[DTA] = CurrentRowDTA
                    && 'Table'[ID] = CurrentRowID
                    && 'Table'[VALUE] = CurrentRowVALUE
            )
        ) > 1,
        1,
        0
    )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataNinja777]

Hi @Cocrodile ,

There are many ways to achive your required outputs and one of them is to use the dax formula like below to create a calculated table which doesn't contain duplicate rows:

Table 2 = calculatetable(distinct('Table'))

I attach a pbix file as an example.

Best regards,

[Cocrodile]

must return all the results with the max value, by necessity I need all the rows because I have other information