Skip to main content
cancel
Showing results for 
Search instead 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

Reply
mngma0102
Frequent Visitor

Creating a Slicer from a Many to One Relationship

Hello,

I am trying to create a matrix visual showing "Open Orders" and "Forecast Value" by "MonthYear". I would like to filter the matrix using "Forecast Version" which is a text field attributed to the forecasting season (ex: Spring2024, Winter2023, etc).  The problem I am running into is the slicer only works for the "Forecast Value" and is showing all "Open Orders" for each month. 

 

I understand that the relationship flow is contributing to the issue but I dont know how to fix it. 

mngma0102_0-1713894740519.png

*Notes - The master data on top is all coming from one data model while Forecast Data is not 

 

3 REPLIES 3
Alex87
Solution Sage
Solution Sage

I confirm the formula provided works correctly. Make sure your relationships are made at day level

for orders I added in PQ a column start of month = Date.StartOfMonth([Order Date])

 

Open-Orders =
CALCULATE(
    SUM(Orders[Open Orders]),
        TREATAS(
            VALUES('Forecast Data'[Date Start of Month]),
            Orders[Start of Month])
            )
 
Alex87_0-1713937454658.png

 

mngma0102
Frequent Visitor

Yes,  that is correct. Here is a sample of my data

Orders

Month-YearOrder DateOpen Orders
Jul-249/2/2024186294
Jul-248/5/2024182313
Sep-239/13/2023168064
Sep-2411/4/2024146798
Aug-239/1/2023128584
Aug-238/30/2023116872

Forecast Data:

Forecast VersionForecast UnitsRegionMonthDate Start of MonthYearMonth-Year
Winter2024 FCST 11471Canada Aug8/1/20242024 Aug-2024
Winter2024 FCST 15420Canada Jul7/1/20242024 Jul-2024
Winter2024 FCST 13086Canada Nov11/1/20242024 Nov-2024
Winter2024 FCST 19886Canada Oct10/1/20242024 Oct-2024
Winter2024 FCST 13554Canada Sep9/1/20242024 Sep-2024
Winter2024 FCST 13081USA Aug8/1/20242024 Aug-2024
Winter2024 FCST 12217USA Jul7/1/20242024 Jul-2024
Winter2024 FCST 1919USA Jun6/1/20242024 Jun-2024
Winter2024 FCST 18952USA Nov11/1/20242024 Nov-2024
Winter2024 FCST 13528USA Sep9/1/20242024 Sep-2024
Winter2024 FCST 22913Canada Aug8/1/20242024 Aug-2024
Winter2024 FCST 21295Canada Jul7/1/20242024 Jul-2024
Winter2024 FCST 27351Canada Jun6/1/20242024 Jun-2024
Alex87
Solution Sage
Solution Sage

Hello,

If I understand correctly you would like to push the filter Forecast Version up to Date table in order to reach Orders. Your model does not allow this, you should use DAX measures instead. It can be done, but it would be very useful if you could share a sample data for the table Forecast Data and Orders.

 

I would give a try to something like this:

Open-Orders =
CALCULATE(
    SUM(Orders[Open Orders]),
        TREATAS(
            VALUES('Forecast Data'[Date]),
            Orders[Order Date])
            )

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors