topic Re: Translating SQL command to DAX Measure Help! in DAX Commands and Tips

Translating SQL command to DAX Measure Help!

bballjoe12 — Mon, 31 Jan 2022 21:59:10 GMT

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!

Re: Translating SQL command to DAX Measure Help!

VahidDM — Mon, 31 Jan 2022 23:21:51 GMT

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/

Re: Translating SQL command to DAX Measure Help!

bballjoe12 — Tue, 01 Feb 2022 00:40:30 GMT

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?

Re: Translating SQL command to DAX Measure Help!

VahidDM — Tue, 01 Feb 2022 01:48:35 GMT

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/

Re: Translating SQL command to DAX Measure Help!

Anonymous — Tue, 08 Feb 2022 07:54:25 GMT

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.