Forum Discussion

netanel's avatar
netanel
Post Prodigy
4 years ago
Solved

Matrix Filed Parmetrs

Hey All!

Maybe you can help me I have an interesting case for you
In Excel my users filter a table with 2 columns but the filters are on the column.
Therefore each column in Excel can vary depending on the filter
The filters are:
1. Time -  Year|Quarter|Month
2. Scenario - Actual|Plan|Forcast
3. Version - Version 1 an version first, Per month

I'm trying to do the same thing in matrix Table
I used Filed Parmetrs But I did not get the desired result

Unfortunately I will not be able to share real data
Sends an example of Excel to explain the image

https://1drv.ms/f/s!AonyYI-TdspHgltwngZ6OKvpnSsc

Need Help
  • netanel's avatar
    netanel
    4 years ago

     Hey All,

    I found the solution
    Multiply the DIMs we want to filter
    And use of Removefilter
    It looks like this:

    Column A = CALCULATE(SUM(Epm_Fct[Amount_N]),
    REMOVEFILTERS('EpmScenarioDescAll'[Scenario]),
    REMOVEFILTERS('EpmVersionDescAll'[Version]),
    REMOVEFILTERS(EpmPlanElement[PlanElementLvl1],EpmPlanElement[PlanElementLvl2],EpmPlanElement[PlanElementLvl3],EpmPlanElement[PlanElementLvl3],EpmPlanElement[PlanElementLvl5],EpmPlanElement[PlanElementLvl6],EpmPlanElement[PlanElementLvl7],EpmPlanElement[PlanElementLvl99] ),
    REMOVEFILTERS(Dim_Time[DateVal].[Date],'Dim_Time'[DateVal].[Day],Dim_Time[DateVal].[MonthNo],Dim_Time[DateVal].[Month],Dim_Time[DateVal].[QuarterNo],Dim_Time[DateVal].[Quarter],Dim_Time[DateVal].[Year]))
    Column B = CALCULATE(SUM(Epm_Fct[Amount_N]),
    REMOVEFILTERS('EpmScenarioDescAll (2)'[Scenario]),
    REMOVEFILTERS('EpmVersionDescAll (2)'[Version]),
    REMOVEFILTERS('EpmPlanElement (2)'[PlanElementLvl1],'EpmPlanElement (2)'[PlanElementLvl2],'EpmPlanElement (2)'[PlanElementLvl3],'EpmPlanElement (2)'[PlanElementLvl4],'EpmPlanElement (2)'[PlanElementLvl5],'EpmPlanElement (2)'[PlanElementLvl6],'EpmPlanElement (2)'[PlanElementLvl7],'EpmPlanElement (2)'[PlanElementLvl99] ),
    REMOVEFILTERS('Dim_Time (2)'[DateVal].[Date],'Dim_Time (2)'[DateVal].[Day],'Dim_Time (2)'[DateVal].[MonthNo],'Dim_Time (2)'[DateVal].[Month],'Dim_Time (2)'[DateVal].[QuarterNo],'Dim_Time (2)'[DateVal].[Quarter],'Dim_Time (2)'[DateVal].[Year]))

    If you have a better solution I would love to hear
    good luck!

     
     
     
     
     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi netanel ,

     

    Could you describe the content of the first table?

    Version, Scen and Date are slicers, then what is this part, current selected slicers?

    The following part is the desired result?

     

    Best Regards,

    Jay

    • netanel's avatar
      netanel
      Post Prodigy
      4 years ago

      Hi Anonymous 
      The result I get from the filter
      Is the money(Amount) that comes from the Fact table

       

      By filtering on each column I get the result in that column
      For example in column A I filter
      Ready
      Forcast
      2022
      And I expect to get the money in the same column
      In column B I will filter something else and expect to get different results
      All this in one table

      • netanel's avatar
        netanel
        Post Prodigy
        4 years ago

         Hey All,

        I found the solution
        Multiply the DIMs we want to filter
        And use of Removefilter
        It looks like this:

        Column A = CALCULATE(SUM(Epm_Fct[Amount_N]),
        REMOVEFILTERS('EpmScenarioDescAll'[Scenario]),
        REMOVEFILTERS('EpmVersionDescAll'[Version]),
        REMOVEFILTERS(EpmPlanElement[PlanElementLvl1],EpmPlanElement[PlanElementLvl2],EpmPlanElement[PlanElementLvl3],EpmPlanElement[PlanElementLvl3],EpmPlanElement[PlanElementLvl5],EpmPlanElement[PlanElementLvl6],EpmPlanElement[PlanElementLvl7],EpmPlanElement[PlanElementLvl99] ),
        REMOVEFILTERS(Dim_Time[DateVal].[Date],'Dim_Time'[DateVal].[Day],Dim_Time[DateVal].[MonthNo],Dim_Time[DateVal].[Month],Dim_Time[DateVal].[QuarterNo],Dim_Time[DateVal].[Quarter],Dim_Time[DateVal].[Year]))
        Column B = CALCULATE(SUM(Epm_Fct[Amount_N]),
        REMOVEFILTERS('EpmScenarioDescAll (2)'[Scenario]),
        REMOVEFILTERS('EpmVersionDescAll (2)'[Version]),
        REMOVEFILTERS('EpmPlanElement (2)'[PlanElementLvl1],'EpmPlanElement (2)'[PlanElementLvl2],'EpmPlanElement (2)'[PlanElementLvl3],'EpmPlanElement (2)'[PlanElementLvl4],'EpmPlanElement (2)'[PlanElementLvl5],'EpmPlanElement (2)'[PlanElementLvl6],'EpmPlanElement (2)'[PlanElementLvl7],'EpmPlanElement (2)'[PlanElementLvl99] ),
        REMOVEFILTERS('Dim_Time (2)'[DateVal].[Date],'Dim_Time (2)'[DateVal].[Day],'Dim_Time (2)'[DateVal].[MonthNo],'Dim_Time (2)'[DateVal].[Month],'Dim_Time (2)'[DateVal].[QuarterNo],'Dim_Time (2)'[DateVal].[Quarter],'Dim_Time (2)'[DateVal].[Year]))

        If you have a better solution I would love to hear
        good luck!