- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Problem using DateTable columns to create Matrix row hierarchy
I am trying to create a Matrix that uses the DateTable fields to create a row hierarchy but the most granular row is the "StartDate" and that is not the date field being used as the relationship between the Fact table and Date table. I saw a mention that Measures can't be used as a row in a matrix so how can I achieve this? Below is an example of the two tables and the hierarchy order I'm trying to acheive. The green header columns are the relationship columns. Any help is appreciated. Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
We are looking at a modeling issue here; but without the bigger picture it is hard to come up with solutions. Is this really all you want to be showing in the Matrix? It is highly unusual to have a matrix without measures. Also, you might want to create a dimension with those IDs in the fact table.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
There are other measures that would appear in the matrix but my issue isn't the values of those measures, its getting that FactTable[StartDate] column to be able to use the DateTable year and month fields as its hierarchy in the rows of a matrix. My only ideas are either add a year column and month column into the FactTable or duplicate a Date/Calendar table which Ive read is a bad idea. Just wondering if anyone else has had a Fact table with two different date columns and wanted to use the one that isn't the foreign key to the DateTable. I've seen Fact Tables with Start Date, End Date, etc and neither are used as the foreign key so are they unable to use DateTable month, year, etc columns for a matrix?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
With that relationship there, you will need to create that inactive relationship, and a measure using that inactive relationship.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
From what Ive read you cant use a measure as a row in a matrix, it has to be a column. which is my issue because I cant add new columns to the dataset I am accessing.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For me it is unclear what you are trying to achieve here. Can you eleborate on the expected outcome? a mockup with values?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Currently since the DateTable aka CalendarTable is using the FactTable[Date] column as its relationship when I use the DateTable fields as a heirarchy every FactTable[StartDate] appears inside each month. This wouldn't be an issue if the DateTable relationship was using the FactTable[StartDate] column as its relationship but it isn't and I can't alter the model aside from creating an inactive relationship or measure. Below is what I get vs what I hope to get
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
07-19-2024 02:55 PM | |||
07-11-2024 12:26 PM | |||
07-11-2024 08:35 AM | |||
03-22-2024 03:25 PM | |||
08-23-2024 03:08 PM |
User | Count |
---|---|
21 | |
18 | |
17 | |
7 | |
5 |
User | Count |
---|---|
32 | |
27 | |
19 | |
13 | |
12 |