Using DAX, I want to get only one value for multiple rows with same ID, and then run an IF statement-CodePudding

I am new to Power BI, I am facing issue where I want to create a new column based on the latest date and the id column

ID     LogCreationDate  Points  What I want
1001    12-Oct-2022     5       null
1001    17-Oct-2022     2       2
1001    13-Oct-2022     7       null
1001    07-Aug-2022     2       null
1002    03-Sept-2022    2.1     null
1002    22-Sept-2022    5       null
1002    04-Oct-2022     1       1
1002    01-Aug-2022     1.2     null
1003    05-Nov-2022     3.5     3.5
1003    01-Nov-2022     6.6     null

In Above table, I want to calculate "What I want" column using DAX, not using power query

CodePudding user response：

Try this Calculated Column:

=
REPT(
    Table1[Points],
    CALCULATE(
        LASTDATE( Table1[LogCreationDate] ),
        ALLEXCEPT( Table1, Table1[ID] )
    ) = Table1[LogCreationDate]
)

CodePudding user response：

I think This is the best job for the measure, not the calc. column:

Please use this code:

what I want =
VAR TblSSS =
    ADDCOLUMNS (
        YourTbl,
        "MaxDate",
            CALCULATE (
                LASTDATE ( YourTbl[LogCreationDate] ),
                ALLEXCEPT ( YourTbl, YourTbl[ID] )
            )
    )
RETURN
    MAXX ( TblSSS, IF ( [LogCreationDate] = [MaxDate], [Points], "null" ) )

If we test it on a table visual, It returns:

Note: After putting all fields and measure into suitable places, do not forget to click the down-pointing arrow on the id field, and pick 'show values with no data'.