Want to convert a sql function into dax for a direct query

Laiq_Rahman — Thu, 07 Mar 2024 00:36:25 GMT

HI,

I want to convert the below sql query column into dax for a direct query option. Can you please help how to do that?

- SUM(BIDS) OVER (PARTITION BY DRID,INTRL_DATE,PERD_ID ORDER BY BND ASC) as CUM_BIDS

- GREATEST(0,BID_AVAIL + LEAST(0,INIAL_MW - CUM_BIDS ))

Regards

Laiq

Re: Want to convert a sql function into dax for a direct query

123abc — Thu, 07 Mar 2024 05:11:23 GMT

To translate the provided SQL function into DAX for a direct query option, you need to understand the logic of the SQL function and then find the corresponding functions and operations in DAX.

Let's break down the SQL functions:

SUM(BIDS) OVER (PARTITION BY DRID,INTRL_DATE,PERD_ID ORDER BY BND ASC) as CUM_BIDS: This is a window function in SQL that calculates the cumulative sum of the column BIDS partitioned by DRID, INTRL_DATE, and PERD_ID, ordered by BND in ascending order.
GREATEST(0,BID_AVAIL + LEAST(0,INIAL_MW - CUM_BIDS )): This function calculates the maximum value between 0 and the sum of BID_AVAIL and the minimum value between 0 and the difference between INIAL_MW and CUM_BIDS.

Now, translating this into DAX, assuming you're working in Power BI or another DAX-supported environment, you can follow these steps:

For the first part (SUM(BIDS) OVER...), you can achieve this using DAX's SUMX and FILTER functions to calculate the cumulative sum within a certain context.

For the second part (GREATEST(0,BID_AVAIL + LEAST(0,INIAL_MW - CUM_BIDS))), you can use DAX functions such as MAXX and MINX to find the maximum and minimum values.

Here's how you can write the DAX expression:

CUM_BIDS =
VAR CumulativeBids =
CALCULATE(
SUM('Table'[BIDS]),
FILTER(
ALL('Table'),
'Table'[DRID] = EARLIER('Table'[DRID]) &&
'Table'[INTRL_DATE] = EARLIER('Table'[INTRL_DATE]) &&
'Table'[PERD_ID] = EARLIER('Table'[PERD_ID]) &&
'Table'[BND] <= EARLIER('Table'[BND])
)
)
RETURN
CumulativeBids

Max_Bid =
MAXX('Table', 0, 'Table'[BID_AVAIL] + MINX('Table', 0, 'Table'[INIAL_MW] - 'Table'[CUM_BIDS]))

In this DAX expression:

CUM_BIDS: It calculates the cumulative sum of the BIDS column partitioned by DRID, INTRL_DATE, and PERD_ID.
Max_Bid: It calculates the maximum value between 0 and the sum of BID_AVAIL and the minimum value between 0 and the difference between INIAL_MW and CUM_BIDS.

Please replace 'Table' with the appropriate table name in your data model. This DAX expression assumes a table structure similar to your SQL query. Adjust column names and table names as per your data model.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

topic Want to convert a sql function into dax for a direct query in DAX Commands and Tips

Want to convert a sql function into dax for a direct query

Re: Want to convert a sql function into dax for a direct query