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

[Laiq_Rahman]

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

[123abc · Accepted solution]

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:

1. 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.

2. 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.