DAX Query for Getting a filtered value

[Anonymous]

I need the DAX query to get the below measure or calculated column. How to achieve ?

Get StatusName from ActionTable WHERE Action_Date = MAX(Action_Date) and Status != 17

[AlB · Accepted solution]

Hi @Anonymous 

Place this measure in a card visual:

GetStatusName =
VAR maxDate_ = MAX ( ActionTable[Action_Date] )
RETURN
    CALCULATE (
        DISTINCT ( ActionTable[StatusName] ),
        ActionTable[Action_Date] = maxDate_,
        ActionTable[Status] <> 17
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[TomMartens]

Hey @Anonymous ,

please excuse, but I do not fully understand your requirement. As "Get StatusName ..." does not indicate if you are looking for a single value (a scalar value) or a table (a single-column table).

The solution @AlB provided might return an error, if more than one StatusName are satisfying the condition. CALCULATE(...) will return a scalar value, whereas DISTINCT() will return a table. There is an implicit datatype transformation happening as a single-column table with one row can be transformed into a scalar value if necessary, here this transformation is triggered by CALCULATE().

Maybe you can take some time and be more specific about the nature of your expected result, if the nature of your business prevents multiple StatusName for the same date and the status <> 17, then, excuse this post.

Regards,

Tom