Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DAX in tabular model - returning specific record from child table based upon ordering Expand / Collapse
Author
Message
Posted Saturday, January 11, 2014 5:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 12, 2014 3:43 AM
Points: 24, Visits: 104
I'm very new to DAX and tabular models and I've managed to get stumped on what will be a fairly common problem that we will face. Consider the following scenario (not my actual scenario, but the same problem in a scenario that is easy to understand without lots of explanation):

Sales and customer tables are linked on Customer_Key. We want to find the Sales_ID for the biggest value sale (Sales_Value) and put that as a column in the Customer table. This will be used in an SSRS report to return the biggest value sale in a date range defined by the end user. I can figure out how to return the biggest value sale, but now how to then use that to return the Sales_ID for that value (keeping in mind that more than one customer may happen to have a sale of the same value).

Were I facing this problem in an SQL query, I'd simply produce a sub-query as follows:

SELECT Sales_ID
FROM
(SELECT Sales_ID, ROW_NUMBER() OVER(PARTITION BY Customer_Key ORDER BY Sales_Value Desc) AS R
FROM Sales
WHERE Sale_Date BETWEEN @ReportStartDate AND @ReportEndDate
) AS Sub
WHERE R = 1

What would be the equivalent DAX solution?

Thanks

Stuart
Post #1530025
Posted Tuesday, January 14, 2014 12:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 12, 2014 3:43 AM
Points: 24, Visits: 104
After a great deal of experimenting I managed to deliver a functioning solution, on very much the same premise as my SQL code. You create a calculated column as follows:

=
CALCULATE (
MAXX (
TOPN (
1,
Sales,
Sales[Sales_Value],
0,
Sales[Sales_ID],
0
),
Sales[Sales_ID]
),
RELATEDTABLE (Sales)
)

What this is basically doing is to order the sales table by Sales_Value (descending) and take the top 1 value (all done by TOPN function) - I added Sales_ID to the sort order in case of tied values. This returns a table with one record per customer (the max sales value, because I ordered by Sales_Value descending), so I just have to pull the column I want from the table. I chose to do this using MAXX, though any function that returns a column value would work because there is only one row per client. I can't recall if I needed the RELATED table argument or not (I'm writing this away from my solution) - I think I did because the direction of the join was incorrect.

Any better suggestions?

Stuart
Post #1530842
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse