Forum Discussion

mngma0102's avatar
mngma0102
Frequent Visitor
2 years ago

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. 

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

 

Need Help
Show and Tell
Tips and Tricks

3 Replies

  • Alex87's avatar
    Alex87
    Solution Sage
    2 years ago

    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])
                )
  • mngma0102's avatar
    mngma0102
    Frequent Visitor
    2 years ago

    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's avatar
    Alex87
    Solution Sage
    2 years ago

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