Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Query for Getting a filtered value

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
Need Help
Show and Tell
  • AlB's avatar
    AlB
    5 years ago

    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.

     

AlB's avatar
AlB
Community Champion
5 years ago

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's avatar
    TomMartens
    Super User
    5 years ago

    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