SQL QUERY TO DAX

[nareshr89]

I would need help to convert the below query to DAX

WITH "SQL1"
AS (
  SELECT "SQL1"."Calnu" AS "Calnu"
    ,"SQL1"."UNITID" AS "UNITID"
  FROM (
    SELECT COUNT(AL1.Calnu) AS Calnu
      ,AL3.UNITID
    FROM GOT AL1
      ,TOT AL2
      ,MOT AL3
    WHERE (
        AL2.TALL = AL1.TALL
        AND AL3.SHORT = AL2.SHORT
        AND AL3.MED = AL2.MED
        )
      AND (
        (
          AL1.STAT = 6
          AND AL1.TYPE = 0
          )
        )
    GROUP BY AL3.UNITID
    ) "SQL1"
  )
SELECT SUM("SQL1"."Calnu") AS "Calnu"
  ,"SQL1"."UNITID" AS "UNITID"
  ,CASE 
    WHEN "SQL1"."UNITID" IN (
        'AT110','AT120'
        )
      THEN 'Drop'
    WHEN "SQL1"."UNITID" IN (
        'BT110'
        ,'BT120')
      THEN 'Half'
    ELSE 'IDEA'
    END AS "Cal_type"
FROM "SQL1"

[v-jianpeng-msft · Accepted solution]

Hi, @nareshr89 

You can type the SQL statement when connecting the SQL database in Power Bi Desktop:

You click on Advance Option >> SQL Statement (optional, requires database). Type your SQL statement in the input box below. Power BI will screen data based on your SQL statement and load data.

In Power Bi, DAX is used to create computing, which is different from SQL in grammar and function. The following is an example based on the SQL query you provided by you.

First of all, you need to create a calculation table or calculating column to represent the sub -query part in the SQL query, and then use this calculation table or column in another calculation column:

DAX formula:

SQL1_calculated = 
CALCULATE(
	SUMX(
FILTER(
‘GOT’,
‘GOT’[STAT] = 6 && ‘GOT’[TYPE] = 0),
COUNTROWS(
FILTER(
‘TOT’,’TOT’[TALL]=’GOT’[TALL])
)
)
	ALLEXCEPT(‘MOT’,’MOT’[UNITID])
)
SQL_Final = 
CALCULATE(
	SUM(‘SQL1_calculated’[CalculatedColumn]),
	ALLEXCEPT(‘SQL1_calculated’, ‘SQL1_calculated’[UNITID])
)& “/”&
SWITCH(
	TRUE(),
	‘SQL1_calculated’[UNITID] = “AT110” || ‘SQL1_calculated’[UNITID] = “AT120”,”Drop”,
	‘SQL1_calculated’[UNITID] = “BT110” || ‘SQL1_calculated’[UNITID] = “BT120”,”Half”,
	“IDEA”
)

This DAX expression may need to be adjusted according to your actual data model. Here I assume that GOT, TOT, MOT is a table in your model, there is a proper relationship between them. SQL1_CALCULED is an auxiliary calculation. It first calculates each UNITID. Calnu value. Then, SQL_FINAL calculates the total CALNU value of each unitid and adds a unitid -based classification.
Please note that the SWITCH function in DAX is used to replace the case statement in SQL. Thellexcept function is used to retain all other contexts other than specific dimensions. Sumx and Filter functions are used to traverse tables and screen and calculate according to conditions.

If you are not familiar with the conversion between DAX and SQL statements, you can click below links to connect to learn basic conversion.

From SQL to DAX: Filtering Data - SQLBI

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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