Forum Discussion

Cocrodile's avatar
Cocrodile
Helper I
2 years ago
Solved

Double row one result

DTAIDVALUE
2024-07-01 12345 8
2024-07-0112345 8
2024-07-01123478

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

    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.

3 Replies

  • DataNinja777's avatar
    DataNinja777
    Super User
    2 years ago

    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's avatar
      Cocrodile
      Helper I
      2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.