Forum Discussion
Transformation with the help of SQL query
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:
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 🙂
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.
1 Reply
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.