Forum Discussion
Direct query - matrix - days until today
Hi Helpers
I am trying to make a matrix like so, where I am comparing a date vs today and creating buckets from them the splitting the count by status
| Days vs today | ||||
| 0-5 | 6-20 | 21-100 | ||
| Statuses | A | 54 | 66 | 55 |
| B | 23 | 48 | 68 | |
| C | 82 | 341 | 12 |
I put the values in, and the rows. But I am struggling with the columns.
A date diff measure doesn't seem to work
It doesn't have to be in buckets, though that is preferred. Just # of days is fine as integers
I have a direct query connection.
Is this possible? I know I can just make a calculated column in import mode, but Direct Queries limitations are stressing me out. But it's needed because we need live data.
Anonymous , Direct Query mode also you can have columns but there are limitations.
In the case of SQL Server, it allowed creating a new column like
Column = DATEDIFF([Sales Date],today(),day)If you want the measure to display as a column/row, You need to have an independent table and create a measure that joins this measure and that table using some group by level
refer video - https://www.youtube.com/watch?v=CuczXPj0N-k
2 Replies
Anonymous , Direct Query mode also you can have columns but there are limitations.
In the case of SQL Server, it allowed creating a new column like
Column = DATEDIFF([Sales Date],today(),day)If you want the measure to display as a column/row, You need to have an independent table and create a measure that joins this measure and that table using some group by level
refer video - https://www.youtube.com/watch?v=CuczXPj0N-k