cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
apentyala
Helper II
Helper II

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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.