dax join

3 Topics

Using a nearest-value lookup to cross-join with some distinct dates
Table TimesheetRecords has work records that also include weekly end-of-perod dates (EndOfPeriodDate), from which I want to take all distinct values, and cross-join them with an EmployeeRates table, which combines employees and their rate changes over time, and each record has an EffectiveDate for the associated rate. What I want to do is create a DAX-generated table EmployeeRatesByPeriod which displays the cartesian product of unique periods and the applicable rate for each distinct employee during that period (below), so that I can sum the cost of an employee for a period (there's a time range slicer on my report that filters EndOfPeriodDate). Note: I'm only using TimesheetRecords to source the weekly end dates...there's otherwise no relationship leveraged between, say, the employees who logged time and the table of employee rates. Here's an exmple showing just the relevant fields to my problem: TimesheetRecords Id WorkDate EndOfPeriodDate ... 8/20/2019 8/24/2019 ... 8/23/2019 8/24/2019 ... 8/29/2019 8/30/2019 EmployeeRates EmployeeName EffectiveDate WeeklyRate Bill Preston 1/1/2018 $1000 Bill Preston 8/26/2019 $1100 Ted Logan 2/2/2018 $1200 EmployeeRatesByPeriod EmployeeName Period WeeklyRate Bill Preston 8/24/2019 $1000 Bill Preston 8/30/2019 $1100 Ted Logan 8/24/2019 $1200 Ted Logan 8/30/2019 $1200 I've looked at using VALUES, DISTINCT, or SUMMARIZE to pull the distinct EndOfPeriodDate values from TimesheetRecords, and elsewhere I've successfully used CALCULATE in a calculated column to pull the WeeklyRate using the nearest EffectiveDate. I thought maybe I could create this table by using GENERATE, but the syntax is escaping me. I'm stuck at the non-working expression below, and here are my key issues: 1.) I know CALCULATE doesn't return a table, so it causes errors with GENERATE. 2.) I don't know the best way to include both EmployeeName and WeeklyRate fields from the EmployeeRates table. EmployeeRatesByPeriod = GENERATE( DISTINCT(TimesheetRecords, TimesheetRecords[PeriodEndDate]), CALCULATE( VALUES(EmployeeRates[WeeklyRate]), TOPN( 1, CALCULATETABLE( EmployeeRates, EmployeeRates[EffectiveDate] <= EARLIER( [PeriodEndDate] ) ), EmployeeRates[EffectiveDate], DESC ) ) ) Any recommendations on a better approach...or how to improve mine?
Solved
cseaman
6 years ago Place DAX Commands and Tips
1.4KViews
0likes
1Comment
DAX Command to merge multiple Columns into One column
Hi, Lets picture a table structure as this: Month Budget_Q1 Budget_Q2 Budget_Q3 Budget_Q4 ---------------------------------------------------------------------------- Jan 100 ..... ....... ....... Feb 100 ..... ...... ........ Mar 100 ..... ........ ....... Apr 110 115 ....... ....... May 110 115 ...... ....... Jun 110 115 ........ ....... Jul 120 122 125 ...... Aug 120 122 125 ...... Sep 120 122 125 ..... Oct 110 115 120 115 Nov 110 115 120 115 Dec 110 115 120 115 ... I'm trying to merge a selection of Rows to get the original budget for that quarter. That means the table should look like Month Budget_Summary ------------------------------------- Jan 100 (From Column Budget_Q1) Feb 100 (From Column Budget_Q1) Mar 100 (From Column Budget_Q1) Apr 115 (From Column Budget_Q2) May 115 (From Column Budget_Q2) Jun 115 (From Column Budget_Q2) Jul 125 (From Column Budget_Q3) Aug 125 (From Column Budget_Q3) Sep 125 (From Column Budget_Q3) Oct 115 (From Column Budget_Q4) Nov 115 (From Column Budget_Q4) Dec 115 (From Column Budget_Q4) This is basically an equivalant to SQL code below: SELECT BUDGET_Q1 FROM table_x WHERE MONTH IN ("Jan","Feb","Mar") UNION SELECT BUDGET_Q2 FROM table_x WHERE MONTH IN ("Apr","May","Jun") UNION SELECT BUDGET_Q3 FROM table_x WHERE MONTH IN ("Jul","Aug","Sep") UNION SELECT BUDGET_Q4 FROM table_x WHERE MONTH IN ("Oct","Nov","Dec") How may I achieve this in DAX? Thanks, Usher
Anonymous
7 years ago Place DAX Commands and Tips
22KViews
0likes
4Comments
Convert SQL to Dax (Count Distinct using Join)
I want perform follwing sql query operation in Powerbi using Measure. Also i want to filter result of these query based on date using date slicer Query 1 : select distinct( DocNo) from InvoiceHistory where StatusDescription IN ('3-WAY MATCH ERROR') and statusid = '9e' and ManualProcessingFlag <> '1' and DocNo Not in (select distinct(DocNo) from InvoiceHistory where StatusDescription IN ('NEW','SUCCESSFULLY PROCESSED') )) I have tried following measure to achieve similar result. Measure = CALCULATE( DISTINCTCOUNT(InvoiceHistory[DocNo]), FILTER((InvoiceHistory), [StatusDescription]="3-WAY MATCH ERROR" && InvoiceHistory[StatusID]="9e" && InvoiceHistory[UpdatedDate] <= max(InvoiceHistory[DocDate]) &&InvoiceHistory[ManualProcessingFlag]<>1 && NOT( (Invoice[DocNo] in ( CALCULATETABLE (DISTINCT(Invoice[DocNo]), FILTER((Invoice),(invoice[StatusDescription]="New" || Invoice [StatusDescription] ="SUCCESSFULLY PROCESSED") ))))))) Please help.
Anonymous
7 years ago Place DAX Commands and Tips
1.2KViews
0likes
1Comment