Need to make a matrix but can't figure out how ..

[truttafisker]

Hi there

I have a bit of a problem ..

I need to make a matrix where I have Categories as a Rows and supplier names that are associated with each category name. Then there should be years as columns and revenue for each supplier for each year .. as shown in the image below. (simplified).


I have tried to draw the relationships between the tables that are in use in the picture below, but my main problem is that I don't have a relationship between Agreement and my Date table .. I could do that, but then the problem is that the Start date or the enddate could have a pretty long span .. it could be startdate = 2020 and endDate 2099 .. and in some it startdate = 2025 and enddate=2026 .. so there are no continuity in those parameters.



The invoice Amount should be the parameter that are summarized to make the revenue under each year and supplier name.

Are there anyone how has an example dax code that could work ??

btw .. it's in as SASS so direct query aren't an option.

[v-echaithra]

Hi @truttafisker ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Best Regards,
Chaithra E.

[v-echaithra]

Hi @truttafisker ,

Try to create a bridge table between Agreement and Date, this is a common pattern for "date-in-range" modeling.
The bridge table creates a row for every date in the agreement’s active period.

You relate that bridge to your Date table and Vendor table.Then, when filtering by year or date in your visuals, the revenue and suppliers correctly reflect only the agreements active on those dates.

FilteredAgreementDates =
VAR MinDate = MINX(Agreement, Agreement[StartDate])
VAR MaxDate = MAXX(Agreement, Agreement[EndDate])
RETURN
FILTER (ADDCOLUMNS (GENERATE (Agreement,CALENDAR (MinDate, MaxDate)),
"Year", YEAR([Date])),
[Date] >= Agreement[StartDate] &&
[Date] <= Agreement[EndDate])

You should create relationships in the data model where the FilteredAgreementDates[Date] column is connected to the Date[Date] column with a many-to-one relationship. Similarly, the FilteredAgreementDates[Vendor No] column should be related to the Vendor[No] column, also using a many-to-one relationship.

Inorder to get the correct filtering:

Use the Date[Year] from the shared Date table.
MergedInvoices[Invoice Date] should also relate to Date[Date].
Make sure your Revenue or Invoices table is related to the Vendor and Date tables too.

Now create a matrix as per your requirement.

Hope this helps,
Chaithra E.

[v-echaithra]

Hi @truttafisker ,

Can you please share the sample PBIX to reproduce the issue, we can help validate the pattern more precisely.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Hope this helps. Please reach out for further assistance.

Thank you.

[truttafisker]

ill try something else .. 
thanks

[v-echaithra]

Hi @truttafisker ,

Thank you @FBergamaschi  for your response.

I hope the information provided is helpful. I wanted to check whether you were able to resolve the issue. Please let us know if you need any further assistance.

Thank you.

[truttafisker]

I don't see any solution only that I should deliver some data .. that I haven't

[FBergamaschi]

Hi @truttafisker,

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here