Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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
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?
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
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:
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@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!
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?
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
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |