cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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!

6 REPLIES 6
Super User

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.

Frequent Visitor

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?

Super User

With that relationship there, you will need to create that inactive relationship, and a measure using that inactive relationship.

Frequent Visitor

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.

Super User

For me it is unclear what you are trying to achieve here. Can you eleborate on the expected outcome? a mockup with values?

Frequent Visitor

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