Forum Discussion

fjjpeeters1976's avatar
fjjpeeters1976
Helper III
1 year ago
Solved

feeding SELECTEDVALUE in IN operator

Hi all,
 
I have created a report in which I want to compare two scenarios with eachother. For this I have created 4 tables (2 for choosing the scenarios and 2 for choosing month/year/quarter/half year). These tables are separate from the date table, so I want to use measures to control the filters. Multiple years / quarters / months can be selected but only one scenario.
I have created an isfiltered function and with values and concatenate I get a string of months/years/quarters ets. I want to feed this in a calculate function with an IN operator. However this does not seem to work. I already created a Year text column in the date table to solve the fact that the IN operator only accepts text and not integer.
Now it seems that the IN operator cannot handle a comma separated list of items as input.
I already tried several things but did not find a solution for this. Any help is welcome.
 
BR Frank
See below the dax that I currently have


 



VAR sen1 = SELECTEDVALUE ( Scenario1[Level0] )
VAR Yearfilt1 =
    ISFILTERED ( Scen1[Year] )
VAR Monthfilt1 =
ISFILTERED ( Scen1[Month] )
VAR Halfyfilt1 =
 ISFILTERED ( Scen1[Half year] )
VAR Quarterfilt1 =
 ISFILTERED ( Scen1[Quarter] )
VAR SelectedYear1 =
    IF ( Yearfilt1, VALUES ( Scen1[Year]))
VAR SelectedMonth1 =
    IF ( Monthfilt1, CONCATENATEX ( VALUES ( Scen1[Month] ), Scen1[Month], "," ) )
VAR SelectedHY1 =
    IF (
        Halfyfilt1,
        CONCATENATEX ( VALUES ( Scen1[Half year] ), Scen1[Half year], "," )
    )
VAR SelectedQuarter1 =
    IF (
        Quarterfilt1,
        CONCATENATEX ( VALUES ( Scen1[Quarter] ), Scen1[Quarter], "," )
    )
VAR calcsen1 =
    CALCULATE (
        [Derivatives for waterfall],
        DimScenarioDetailed[Level0] = sen1,
        'Date'[YearText] IN { SelectedYear1 },
                SWITCH (
            TRUE (),
            Monthfilt1, 'Date'[Month] IN { SelectedMonth1 },
            Quarterfilt1, 'Date'[Quarter] IN { SelectedQuarter1 },
            Halfyfilt1, 'Date'[HY] IN { SelectedHY1 },
            Yearfilt1, 'Date'[YearText] IN { SelectedYear1 }
        )

 

    )
RETURN
    calcsen1
  • BA_Pete's avatar
    BA_Pete
    1 year ago

    Hi fjjpeeters1976 ,

     

    Difficult to give an exact answer without more information, but I think you'd want to use relationships here.

    Relate your time period/scenario tables to your fact tables (using inactive relationships if there's conflicts with the rest of the model) then activate relationships you need with USERELATIONSHIP(), and deactivate ones that are conflicting on activation using CROSSFILTER(Table1, Table2, none).

    This should avoid the need to pass lists into CALCULATE filters, as ISFILTERED will be implicit via the relationships.

     

    Hope this makes sense. If this doesn't work for you, then you may need to provide some more information around what your model looks like and why the proposed alternative won't work in your scenario.

     

    Pete

3 Replies

  • fjjpeeters1976's avatar
    fjjpeeters1976
    Helper III
    1 year ago

    Hi Pete, after thinking about your remarks during the weekend I realized it is indeed possible with active and inactive relationships and an additional date table. I was making it unnecesary complex. So now it works. Thanks for your suggestion.

  • BA_Pete's avatar
    BA_Pete
    Super User
    1 year ago

    Hi fjjpeeters1976 ,

     

    Difficult to give an exact answer without more information, but I think you'd want to use relationships here.

    Relate your time period/scenario tables to your fact tables (using inactive relationships if there's conflicts with the rest of the model) then activate relationships you need with USERELATIONSHIP(), and deactivate ones that are conflicting on activation using CROSSFILTER(Table1, Table2, none).

    This should avoid the need to pass lists into CALCULATE filters, as ISFILTERED will be implicit via the relationships.

     

    Hope this makes sense. If this doesn't work for you, then you may need to provide some more information around what your model looks like and why the proposed alternative won't work in your scenario.

     

    Pete

    • fjjpeeters1976's avatar
      fjjpeeters1976
      Helper III
      1 year ago

      Hi Pete, the issue is that I do not just want to show a certain scenario, but that I want to show deviations between the scenarios. I want to create a waterfall chart for the differences between two scenarios.
      In our company we have many scenarios for example every month we create a new forecast scenario, we also have budgets etc. So we want to see what is difference for example between a forecast scenario and a budget scenario. But I want to make it flexible so that user can either see difference by month in a waterfall, or by quarter, or first half year vs second half year etc. I don't think that can be done by using relationships, or?