Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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!
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?
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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
29 | |
23 | |
22 | |
22 |