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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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

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

 




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!




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

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])
            )



Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!




Helpful resources

Announcements
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! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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