Translating SQL command to DAX Measure Help!

[bballjoe12]

I'm trying to create a DAX measure that would grab me the values from a table similar to what this SQL command would do.

SELECT TicketNumber

WHERE Group LIKE "DS_*"

AND GroupChangeFlag >=1

AND GroupSeqNum > 1

I am failing miserably and have looked all over the internet.  If the measure would just display the Ticket Numbers that would be perfect!

Thanks!

[Anonymous · Accepted solution]

Hi @bballjoe12 ,

You can create a measure.

Measure = 
IF (
    LEFT ( MAX ( 'Table'[Group] ), 2 ) = "DS"
        && RIGHT(MAX('Table'[Group]),1)= "*"
        && MAX ( 'Table'[GroupChangeFlag] ) >= 1
        && MAX ( 'Table'[GroupSeqNum] ) > 1,
    1
)

Sample data

Put the measure into visual level filters, set show items when the value is 1.

Best Regards,

Stephen Tao

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

[bballjoe12]

Hello! Thank you for the response.  The table that I am using is currently part of a larger dataset, that I am not the owner of.  It seems that the New Table icon is grayed out and I can only create a new measure or a quick measure.  Can I still use your syntax by creating a new measure?

[VahidDM]

@bballjoe12 

You can use the filter section in your measure to find the result

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[VahidDM]

Hi @bballjoe12 

Is it Access Code?
You can use this to create a new table with one column as Ticket Numbers and all filters:

New Table =
CALCULATETABLE (
    VALUES ( table[TicketNumber] ),
    FILTER (
        table,
        LEFT ( table[Group], 2 ) = "DS"
            && LEN ( table[Group] ) >= 3
            && Tble[GroupChangeFlag] >= 1
            && table[GroupSeqNum] > 1
    )
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/