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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.