question

lukizw-5857 avatar image
0 Votes"
lukizw-5857 asked lukizw-5857 action

Date table for dimensions without any measure / SSAS Tabular

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered LukasYu-msft commented

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


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Lukas, do you have any ideas after I posted the screenshots? Is it a bug or do I just understand the Calendar table wrong?

BR and thanks

0 Votes 0 ·

I haven't tried the sceen as Alexei talked that the engine being lazy. So I can't talk about that. But have you tried making the relationship bidirectional, see if it works?

0 Votes 0 ·

Hi, thanks. So I think I can't solve this problem the way I like to :)

(Bi-directional didn't change anything).

BR and have a great day

Lukas

0 Votes 0 ·
Show more comments
AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

lukizw-5857 avatar image
0 Votes"
lukizw-5857 answered lukizw-5857 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I see a couple workarounds here: invent some measure for the table, or create the timeline off ReportingDate.

2 Votes 2 ·

Yeah, I tried that (timeline on ReportingDate) but it just let's me add a slicer.

Timeline is only available for the Calendar Table (Excel 365 v2102), with ReportingDate I can only choose the default slicer seen in the screenshots above.

:(

0 Votes 0 ·