Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
IM_TRYING_HERE
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!

 

IM_TRYING_HERE_0-1713538388327.png

 

6 REPLIES 6
sjoerdvn
Super User
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.

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?

sjoerdvn
Super User
Super User

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

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. 

sjoerdvn
Super User
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?

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

 

IM_TRYING_HERE_1-1713542810703.png

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.