Forum Discussion
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 depend...
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 heargood luck!
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
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
If you have a better solution I would love to hear
good luck!