Transformation with the help of SQL query

[Anonymous]

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

SalesforceMerge Table (Primary Table or Master Table) with Term start date and term end date.SalesforceMerge Table (Primary Table or Master Table) with Subscriber NumberUsageagg Table with subscriber Number (Duplicate Values, Ordered Date ,Value)

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

Thanks in advance 🙂

[Anonymous · Accepted solution]

@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.