Date table for dimensions without any measure / SSAS Tabular

luki zw 21 Reputation points
2021-04-01T14:14:58.473+00:00

Hello,

it may be an awkward question, but I've tried every way which is known to me and can't figure out how or if its even possible.

(SSAS Tabular, Compatibility level 1500, SQL Server Enterprise)

I have a table, let's call it EmployeesMonthly where I have all my employees for each reporting date (each last of month). Those are just dimensions like JobTitle, BranchOfficeNumber and so on including the ReportingDate. NO MEASURE / FACT.

Sample:

ReportingDate | EmployeeNo | JobTitle | BranchOfficeNumber

2018-01-31 | 123456 | Plumber | 123
2018-02-28 | 123456 | Plumbing manager | 125

and so on...

I've also created a DateTable with all Dates from 2018-01-31 to 2020-12-31 (yes, i marked it as a date table) and connected the Date column to my ReportingDate column from EmployeesMonthly.

It works well with measures, as expected, but what I additionally want to achieve is the possibility that the users (e.g. in an Excel Cube) can go back in history and just see the dimensions for a specific date. THIS ALSO WORKS when i filter the "ReportingDate" Column to the specific date. BUT - although related - selecting a different date in the Date Table doesn't change a thing and lists all 250k possible combinations...

So, long story short: Is it even possible to filter dims with a DateTable or did i get it all wrong and it is just used in connection with measures?

BR and tia

Lukas

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,245 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,816 Reputation points
    2021-04-02T03:00:57.223+00:00

    Hi Lukas,

    The dimension and fact difference has been depreciated in Tabular model, they are all treated like table and handled by DAX.

    The “dim” table can be filtered by the datetable, you need to build relation ship between the two table. If they are intermediately connected, you need to watch out the relationship direction which can affect the filter direction. (Reference: relationships for tabular and pbi)

    If you still find this issue, please give us screenshot of you table relationship, we will see how to help further.

    Regards,
    Lukas

  2. Alexei Stoyanovsky 3,416 Reputation points
    2021-04-02T07:28:37.743+00:00

    The engine is being somewhat lazy here: without a measure, it doesn't bother to consider related tables and filters therein, but still reacts to filters on columns in the same table. Runs contrary to my expectations of how an expanded table should behave, but oh well.

    0 comments
  3. luki zw 21 Reputation points
    2021-04-02T07:31:18.117+00:00

    Hi Alexei, thanks! So I don't do anything wrong, it just isn't possible the way I like it?