How to Create Virtual Relationships between type 2 dimensions avoiding fact table

[gottapu]

Hello,

We have a scenario where our Fact table does not hold future values. Our tabular model is set so that dimensions such as Employee, Assignments, Salary, FTE are connected to our Assignments Fact table. Assignment dimension has assignments that start in the future as well, FTE dimension has future FTE values as well and salary dimension has future salary numbers as well. We have a requriment to compare Assignments current salary, FTE with a future date salary and FTE and find out variance. This requirement is not a typical requirement as most of our reporting is done as of today or in the past.

Also Assignments, FTE and Salary tables are type 2 with each having their own start and end dates. We looked at TREAT AS to create a virtual relationship but the problem is there can be many entries for an assignment, for example in FTE or salary table. How can we find an entry that matches a sepecific day in future that we are looking for? For example we want to find out Assignments and their FTEs and as well as corresponding salaries for a given date in future such as 7/4/2022. 

We do have a date dimension thats connected to fact table as well that I have not shown in the attached pic.

Appreciate any guidance. Attached is a sample table scenario. We cannot create physical relationships between these tables as they are connected to fact table in Analysis services model.

Thanks,

-Raj

[Icey · Accepted solution]

Hi @gottapu ,

You may also try to use TREATAS function. It applies the result of a table expression as filters to columns from an unrelated table.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@gottapu , Based on what I got so far, You can use an independent date table. Using that you can filter data in each table.

example

//Date1 is independent Date table,

new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = mainx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Date] >=_min && 'Table'[Date] <=_max))

such measure can be show other across other common dimensions or with their own fact/dimension columns

same I can pass any selected value of dimension to create a measure for another dimension