Transformation with the help of SQL query

Anonymous — Thu, 07 Jan 2021 10:11:39 GMT

Hello,

I have a small problem with PBI. I have 2 tables namely 'Salesforcemerge' which contains Subscriber Number, Term Start Date, TermEnd Date, etc, and 'UsageFullAgg ' which contains Subscriber Number (with duplicate values) , Ordered Date, Value as shown in the picture. I want to merge them in a single file so that I can get aggregate values of Value Column present in 'UsageFullAgg ' table.

The sample sql query is something like this:

select
A.VERSION,A.NAME,A.NAME_ACCOUNT,A.TERMSTARTDATE,A.TERMENDDATE,A.STATUS,A.BUSINESSSUBSCRIPTIONNUMBER__C,A.MASTERBUSINESSSUBSCRIPTIONNUMBER__C ,
SUM( CASE WHEN b.ORDERED_DATE> REPLACE(A.TERMSTARTDATE,'-','') AND B.ORDERED_DATE<REPLACE(A.TERMENDDATE,'-','') THEN SUM_VALUE ELSE 0 END ) HISTORICAL_VALUE
from (
SELECT DISTINCT A.VERSION,A.NAME,A.NAME_ACCOUNT,A.TERMSTARTDATE,A.TERMENDDATE,A.STATUS,A.BUSINESSSUBSCRIPTIONNUMBER__C,A.MASTERBUSINESSSUBSCRIPTIONNUMBER__C FROM zuora_processed A
where A.name='S75000001749'
) a
inner join usage_full_agg b on b.SUBSCRIBER_NUMBER=a.BUSINESSSUBSCRIPTIONNUMBER__C and statut='H' --and b.subscriber_number='845000259'
GROUP BY A.VERSION,A.NAME,A.NAME_ACCOUNT,A.TERMSTARTDATE,A.TERMENDDATE,A.STATUS,A.BUSINESSSUBSCRIPTIONNUMBER__C,A.MASTERBUSINESSSUBSCRIPTIONNUMBER__C

Can someone tell me how can I implement the same rule in Power BI?

Thanks in advance 🙂

Re: Transformation with the help of SQL query

Anonymous — Mon, 11 Jan 2021 05:45:58 GMT

Anonymous

You can only implement these step by step, there are different methods, a short guide:

1. Group by in the Query Editor

2. Inner Join

3. Create a Distinct table when A.name='S75000001749', for example:

Table = CALCULATETABLE(DISTINCT('Tablename'[Columnname]),FILTER('Tablename',[A.name]='S75000001749'))

4. For the conditional sum expression, you can create a column using DAX with IF function also there is a REPLACE function.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

topic Re: Transformation with the help of SQL query in Report Server

Transformation with the help of SQL query

Re: Transformation with the help of SQL query