Frequent Visitor

7 years ago

Solved

SQL to DAX: Get SalesPerson Region on a specific date into a measure

I have been working in SQL and SSRS for several years now but i am new to PowerBI. I am having difficulty creating a measure. I have below tables:

1. Order

2. SalesPersonTeam

In Order table i have a column "Salespersonid" which identifies the salesperson for the order. In the SalesPersonTeam Table i have the salesperson and team it was assigned with startdate and enddate for the assignment. Below is my relationship:

Order Table:

datestamp	orderid	salespersonid	amount	unit
1/1/2018	117	4	        40	1
1/2/2018	118	2	        20	6
2/5/2018	119	1	        500	60
2/20/2018	120	2	        100	20
3/6/2018	121	1	        20	5
3/11/2018	122	3	        50	10
3/18/2018	123	1	        10	2

SalesPerson Table:

Salespersonid	SalesPersonName
1	        John
2	        Mark
3	        Steve
4	        Chris

SalesPersonSalesTeam Table:

SalesPersonSalesTeamID	SalesPersonid	TeamID	TeamName	StartDate	ExpirationDate	SalesGroup
11	                1	        1001	US	        01/01/2018	02/20/2018	West
12	                1	        1002	AUS	        02/21/2018	03/15/2018	South
13	                1	        1005	IND	        02/21/2018	null	        East
21	                2	        1001	US	        01/01/2018	02/01/2018	West
22	                2	        1003	UK	        02/02/2018	null	        North
31	                3	        1004	CAN	        01/01/2018	null	        North
41	                4	        1005	JPN	        01/01/2018	null	        East

Now i want to create a mesaure which i can use to show how much sales was done in different sales group by each sales person. In TSQL i will write the query like:

SELECT * FROM Order O

LEF JOIN SalesPersonSalesTeam SPST ON SPST.Salespersonid = O.Salespersonid and O.datestamp BETWEEN SPST.StartDate AND SPST.ExpirationDate

I am learnign DAX and I tried RELATED, FILTER, CROSSFILTER but all are giving me error. I understand that i can create a sales group as calulated column in order table but i want to create it as measue because my order table have 16 Million records and may hinder the performance/storage. Is this the right approach to get corresponding SalesGroup for an Order? Can you Help?

Need Help

v-juanli-msft

7 years ago

Hi P_R

Create relationships as below, reference here

Create measures in SalesPersonSalesTeam table

Start = MAX([StartDate])

end = IF(MAX([ExpirationDate])=BLANK(),DATE(2999,12,31),MAX([ExpirationDate]))

Measure = CALCULATE(SUM('Order'[amount]),FILTER('Order',[datestamp]>=[Start]&&[datestamp]<=[end]))

Best Regards

Maggie

SQL to DAX.pbix104 KB

7 Replies

v-juanli-msft
Community Support
7 years ago
Hi P_R

Create relationships as below, reference here

Create measures in SalesPersonSalesTeam table

Start = MAX([StartDate]) end = IF(MAX([ExpirationDate])=BLANK(),DATE(2999,12,31),MAX([ExpirationDate])) Measure = CALCULATE(SUM('Order'[amount]),FILTER('Order',[datestamp]>=[Start]&&[datestamp]<=[end]))

Best Regards

Maggie

SQL to DAX.pbix104 KB
- P_R
  Frequent Visitor
  7 years ago
  Thank you for your reply. I think this will work for the question i asked. I have few more bits after getting the measure which is throwing me error. My question remains the same.
  
  Your solution provides me a mesaure that is calculating amount on the salespersonsalesteam table but is there a way i can create a measure which shows corresponding Sales Group in Order table for each row?
  - v-juanli-msft
    Community Support
    7 years ago
    Hi P_R
    
    Sorry for inconvenience.
    
    I find a workaround.
    
    Create the relationships like this
    
    Create measures
    
    Start = VAR condition1 = CALCULATE ( MAX ( SalesPersonSalesTeam[ExpirationDate] ), ALLEXCEPT ( SalesPersonSalesTeam, SalesPersonSalesTeam[SalesPersonid] ) ) RETURN IF ( ISBLANK ( MAX ( [ExpirationDate] ) ), IF ( MAX ( SalesPersonSalesTeam[StartDate] ) < condition1, condition1, MAX ( SalesPersonSalesTeam[StartDate] ) ), MAX ( [StartDate] ) ) end = IF(MAX([ExpirationDate])=BLANK(),DATE(2099,1,1),MAX([ExpirationDate])) flag1 = IF(MAX('Order'[datestamp])<=[end]&&MAX('Order'[datestamp])>=[Start],1,0) flag2 = IF(MAX(SalesPersonSalesTeam[SalesPersonid])=MAX('Order'[salespersonid]),1,0)
    
    Add 'SalesPersonSalesTeam' [SalesGroup] in the 'Order' table, add [flag1] and [flag2] in the Visual level filter.
    
    Please refer to my pbix->page2
    
    Best Regards
    
    Maggie
    SQL to DAX_v2.pbix111 KB
zoloturu
Memorable Member
7 years ago
P_R,

What if you create a calculated column with below logic?

Column = SUMX( FILTER('order', [datestamp] >= [StartDate] && ( [datestamp] <= [ExpirationDate].[Date] || ISBLANK([ExpirationDate])=TRUE() ) && 'Order'[salespersonid] = SalesPersonTeam[SalesPersonid] ) ,'Order'[amount] )

Then you will have a result like below:

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
- P_R
  Frequent Visitor
  7 years ago
  In my question i mentioned that i understand that i can create custom column but since my order table have more than 16 million rows i was hoping if i can achieve this with a mesaure?
  - LivioLanzo
    Solution Sage
    7 years ago
    Hi P_R,
    
    at the end it will still be a measure to calculate the numbers, and it will be a simple measure such as SUM(Orders[Amount])
    
    The calculated column helps you in this case write an easier and faster DAX measure

LivioLanzo

Solution Sage

7 years ago

Hello P_R,

it appears you have Slow changing dimensions going on there. The approach I would suggest is to take the SalesPersonTeam and table and add an Index column to uniquely identify each row, you can either do this with SQL or with Power Query:

Download the file: https://1drv.ms/u/s!AiiWkkwHZChHjzd1LWOZqbC3L-kk

= Table.AddIndexColumn(#"Changed Type", "SalesPersonKey", 1, 1)

= Table.TransformColumnTypes(#"Added Index",{{"SalesPersonKey", Int64.Type}})

Afterwards, you'd need to add this SalesPersonKey into the Order Table, in Power Query you can do it like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY5BDsAgCAT/wtkEFqu1b2n8/zfaRVsOkJiZXbxvgULdMKQIcL774BifMgu5J+d2DnkP7tqSXxEr0iwE24bbr0SSFQjFl1K1p4FdEmrbHPlLZ7zGlej5jJFG3RVY1+Z8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [datestamp = _t, orderid = _t, salespersonid = _t, amount = _t, unit = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"datestamp", type date}, {"orderid", Int64.Type}, {"salespersonid", Int64.Type}, {"amount", Int64.Type}, {"unit", Int64.Type}}),
    
    AddKeyColumns = Table.NestedJoin( ChangedType, "salespersonid", SalesPersonTeam, "SalesPersonid", "KeyTable", JoinKind.Inner),
    ExpandedKeyTable = Table.ExpandTableColumn(AddKeyColumns, "KeyTable", {"StartDate", "ExpirationDate", "SalesPersonKey"}, {"StartDate", "ExpirationDate", "SalesPersonKey"}),
    ReplacedValue = Table.ReplaceValue(ExpandedKeyTable,null,#date(9999, 12, 31),Replacer.ReplaceValue,{"ExpirationDate"}),
    
    SelectRows = Table.SelectRows(ReplacedValue, each [datestamp] >= [StartDate] and [datestamp] <= [ExpirationDate] )
in
    SelectRows

Then you can build your model:

and then add the matrix:

Forum Discussion

SQL to DAX: Get SalesPerson Region on a specific date into a measure

7 Replies

Related content

Make measure ignore specific filter

Specific matrix

Specific Measure Query - Overtime Cost based on Job Role

Matrix with dynamic years only for specific measures (hide other measures per year)

Top 5 Salespersons