Forum Discussion

thampton's avatar
thampton
Icon for Helper III rankHelper III
7 years ago

SUMMARIZE DAX help needed

I have a data model in SSAS that i am bringing into Power Bi to create visuals. I have an Sales table with a connected financials table, based on the Sales ID. I also have dates in the sales table.  ...
Need Help
Show and Tell
Tips and Tricks
Cmcmahan's avatar
Cmcmahan
Icon for Resident Rockstar rankResident Rockstar
7 years ago

You should be able to do this pretty easily.  Once you have the tables related by salesID, create the new column in your summary table and set it up like this:

BestMonth = MAX(RELATED('SalesTable'[sales]))

Since the table SummaryTable is related to the FinancialsTable by a many:1 relationship on Sales ID, and the FinancialTable is related to the SalesTable by a 1:many relationship on Sales ID, PowerBI is able to make that connection straight to the SalesTable.  As long as the Sales ID is populated, when you use the RELATED function it will return a list of filtered values that are related to the current row. Add in the MAX function to find the top value, and you're there!