How to generate UNION report based on two tables using data model

[apentyala]

Dear All,

Please advice, how to generate a union report using exiting Power BI model. below is sample SQL which I need to generate a report in Power BI. it is possible to hardcode as column values?

SELECT
P.NAME AS NAME,
SUM(QUANTITY) AS QUANTITY
FROM
transactions T1
INNER JOIN PRODUCT P1
ON
T1.PRODUCT_ID=P1.ID
WHERE
T.trx_redeem=1 AND T.trans_class='ITEM'

UNION all

SELECT
'POINT' as NAME,
COUNT(*) AS QUANTITY
FROM
transactions T1
INNER JOIN PRODUCT P1
ON
T1.PRODUCT_ID=P1.ID
WHERE
T.trx_redeem=0 AND T.trans_class='POINT'

Kindly suggest how to generate the report using power bi model.

Regards,

Avinash

[v-shex-msft · Accepted solution]

Hi @apentyala,

If you are work with SQL data connector, you can directly use your t-SQL script in advanced SQL statement. (advanced option -> SQL statement)

Power BI Introduction: Working with SQL Server data in Power BI Desktop — Part 5 

If you want to do these with Dax, you can refer to the following blog about join in Dax:

From SQL to DAX: Joining Tables 

After you reference the core on table fields to the current table with conditions, you can use SELECTECOLUMNS to pickup specific fields and use the UNION function to merge these tables.

SELECTCOLUMNS 

UNION 

Regards,

Xiaoxin Sheng