Forum Discussion
How to generate UNION report based on two tables using data model
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
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.
Regards,
Xiaoxin Sheng
1 Reply
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.
Regards,
Xiaoxin Sheng