topic relating or looking up values in virtual tables in DAX Commands and Tips

relating or looking up values in virtual tables

mgrayTCB — Mon, 07 Feb 2022 13:55:03 GMT

I have vitual tables each one shows the expected sales dates for a particular project. One is based on the budgeted date and the other is based on the current dates. I want to compare the 1st current date to the 1st budget date and then the 2nd current date to the 2nd budget date, and so on. I created a meausre that indexes them 1,2,3,n [CENumber] with the thoguht that I could match them up and calcualte the difference in dates.

I cant seem to figure out how to relate or lookup the date values between these tables in order to calcualte the difference and then sum the total delay. Any help would be appreciated

Below is the code that gets me each of the tables for a particular project and year but I am stuck on how to relate or look up dates between them to calculate the difference.

var _projname ="Marshall Gardens"

var _TableCurrent =

ADDCOLUMNS(

SELECTCOLUMNS(

CALCULATETABLE(

FILTER(

dimDate,

[NetFeeCurrent]>0),

// filter down to year and prject

FILTER(dimDate,dimDate[Date].[Year] = 2022),

FILTER(Project,Project[ProjectName] = _projname)

"dateCurrent",[Date]),

"CENumber",[NumberSelectedDates]

)

var _TableBudget =

ADDCOLUMNS(

SELECTCOLUMNS(

CALCULATETABLE(

FILTER(

dimDate,

[NetFeeBudget]>0),

// filter down to year and prject

FILTER(dimDate,dimDate[Date].[Year] = 2022),

FILTER(Project,Project[ProjectName] = _projname)

"dateBudget",[Date]),

"CENumber",[NumberSelectedDates]

)

Re: relating or looking up values in virtual tables

amitchandak — Mon, 07 Feb 2022 15:15:13 GMT

mgrayTCB , when you have the common date and project taking a diff measure at a visual level between measures from two tables for the displayed column

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: relating or looking up values in virtual tables

mgrayTCB — Mon, 07 Feb 2022 15:55:33 GMT

Thank you for thinking about this. Here is a bit more detail. I have a budget table(DevRevenueBudget) and a Current table (DevRevenue). Budget was frozen at the begining of the year. Each project has a series of sales events they were sechedule on cetrain dates at budget time but now have updated (current dates). I want to calculate the delay. See model and expected result.

Re: relating or looking up values in virtual tables

v-kkf-msft — Thu, 10 Feb 2022 06:19:38 GMT

Hi mgrayTCB ,

Not sure if I understand correctly. I create the following example data. =

Then add index to pre group.

#"Grouped Rows" = Table.Group(#"Changed Type", {"ProjectID"}, {{"Group", each _, type table [Date=nullable date, ProjectID=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn(Table.Sort([Group],{{"Date", Order.Ascending}}), "Index", 1, 1, Int64.Type)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Date", "ProjectID", "Index"}, {"Date", "ProjectID", "Index"})

Create calaulated table [Index] and create relationship with table [DevRevenueBudget] and [DevRevenue].

Index = DISTINCT ( UNION ( VALUES ( DevRevenue[Index] ), VALUES ( DevRevenueBudget[Index] ) ) )

Then create the measure.

Measure = DATEDIFF ( SELECTEDVALUE ( DevRevenue[Date] ), SELECTEDVALUE ( DevRevenueBudget[Date] ), DAY )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: relating or looking up values in virtual tables

mgrayTCB — Thu, 10 Feb 2022 14:28:46 GMT

This is very helpful. I will work through this solution with my data and let you know if I have any questions

Re: relating or looking up values in virtual tables

mgrayTCB — Thu, 10 Feb 2022 17:36:57 GMT

I am working though this and trying to add the index to the revenue table. I got the grouping step to work but cant get the index step to work. I keep getting this error:

This is the M code I am trying to add in the Add Custom column step.

Table.AddIndexColumn(Table.Sort[Group],{{"DateActOrEst",Order.Ascending}},"Index",1,1,Int64.Type)

This was taken right from your example other than the name of the date field.

Re: relating or looking up values in virtual tables

v-kkf-msft — Fri, 11 Feb 2022 07:52:45 GMT

Hi mgrayTCB ,

I think you're ignoring the parentheses of Table.Sort function. Please try this:

= Table.AddIndexColumn( Table.Sort( [Group], { {"Date", Order.Ascending} } ), "Index", 1, 1, Int64.Type )