Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

HELP ME CONVERT A SQL QUERY TO DAX PLEASE

Can somebody please help me convert the below query to DAX?

 

WITH added_row_number AS (
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY [company_key], [issuer_key], [account_number], [account_currency_key] ORDER BY [balance_date_key] DESC) AS row_number
FROM treasury.cash_balance_fact
)
SELECT
*
FROM added_row_number
WHERE row_number = 1
order by [company_key], [issuer_key], [account_number], [account_currency_key];

Need Help
Tips and Tricks

1 Reply

  • TotunG's avatar
    TotunG
    Icon for Resolver I rankResolver I
    3 years ago

    I don't know much SQL but ChatGPT might be useful: 

     

    EVALUATE
    VAR AddedRowNumber =
    ADDCOLUMNS (
    treasury.cash_balance_fact,
    "row_number",
    RANKX (
    FILTER (
    treasury.cash_balance_fact,
    EARLIER ([company_key]) = [company_key] &&
    EARLIER ([issuer_key]) = [issuer_key] &&
    EARLIER ([account_number]) = [account_number] &&
    EARLIER ([account_currency_key]) = [account_currency_key]
    ),
    [balance_date_key],
    ,
    DESC
    )
    )
    VAR FilteredResults =
    FILTER ( AddedRowNumber, [row_number] = 1 )
    RETURN
    ORDER BY [company_key], [issuer_key], [account_number], [account_currency_key]