topic Re: Calculated Column filter dynamic in DAX Commands and Tips

Calculated Column filter dynamic

Anonymous — Wed, 16 Aug 2023 16:48:25 GMT

Hi
I have created a new column with dax this way :

MCS = VAR CurrentKey = 'Release planner'[FC ID] VAR MultifactorFiltered = FILTER('Multifactor PI Calculator', 'Multifactor PI Calculator'[Multifactor PI] = "2023PI3") VAR MatchingKey = COUNTROWS(FILTER(MultifactorFiltered, 'Multifactor PI Calculator'[FC number] = CurrentKey)) VAR DividerToUse = IF( MatchingKey > 0, MAXX(MultifactorFiltered, [Value]), 'Release planner'[FC size] ) RETURN IF( DividerToUse < 1, 'Release planner'[MCS Effort] * DividerToUse, 'Release planner'[MCS Effort] / DividerToUse )

There reason behind this is that sometimes the column need to be calculated from a different table. I am using this column on different tabs as well and it needs to be able to change by everytab. In the line 3 "2023PI3" part needs to be dynamic in someways, slicer filter not sure.
I have tried to create a new table with just these values and use it as a slicer but it would change the end value.
Thanks inadvance

Re: Calculated Column filter dynamic

Anonymous — Tue, 22 Aug 2023 06:21:59 GMT

Hi Anonymous ,

If you wish to replace "2023PI3" with the value selected by the slicer, you need to
1. Create a measure instead of a calculated column, because calculated columns cannot change dynamically.
2. Create a slicer table.

3.Change "2023PI3" in the metric to SELECTEDVALUE('Slicer Table'[Value]), which will change depending on the value of the slicer.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Re: Calculated Column filter dynamic

Anonymous — Tue, 22 Aug 2023 08:08:17 GMT

Thanks for the help, it worked , do you mind helping me with this part as well? I want to create a running total of this measure, for which I have an index column for the order. This was the older version:

MCS RT = VAR currentIndex = MAX('Release planner'[Index]) RETURN CALCULATE( SUM('Release planner'[MCS]), FILTER( ALLSELECTED('Release planner'), 'Release planner'[Index] <= currentIndex ) )

Many thanks already

Re: Calculated Column filter dynamic

Anonymous — Tue, 22 Aug 2023 08:39:54 GMT

Hi Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.

(2) We can create a measure.

total = SUMX(ALL('Release planner'[Index]),[MCS RT])

(3) Then the result is as follows.

Best Regards,

Neeko Tang

Re: Calculated Column filter dynamic

Anonymous — Tue, 22 Aug 2023 13:31:57 GMT

Yes this is what I want to achive, but as in the first space we created a measure for MCS so we can't use the SUM function in line 5 fro MCS RT.