Remove and Add filter to Single Column in Table

[Krish_A]

Hi Bi Community,

Issue

I have a gain/loss column from a table visual (table x) which I'd like to add to another table visual (table y). The issue is table y has a slicer and filter applied to it which I do not desire to be applied to the gain/loss column.

Desire:

- I would like to know how I could add the gain/loss column to table y and remove the slicer/filter for the gain/loss column only.

- Want to add the gain/loss column from table x to table y with the following conditions:

          1) Remove the table y slicer and filter just for the gain/loss column

          2) Apply the date filter from table x ONLY to the gain/loss column 

Context:

- The gain/loss column is an average value in table x - it has a date filter applied to it (last 12 months) which I will require 

- Table y has a slicer and filter applied to it - I do not want these applied to the gain/loss column

- Both tables have a common category column called Trade Names. 

Out of context, what I'm after is applying/removing different filters for a column within a table visual.

I have played around with DAX by removing filters and trying to add the date filter but am unsuccessful. Regarding the date filter, the column is not actually a date table but rather a column containing "approved at" dates. As a result of using datebetween or inperiod, i get the error regarding duplicate dates - how could I work around this for the solution im chasing?

Apologies for not posting data as it is confidential. A generalised answer on how to remove/apply filters & slicers to columns would be more than enough.

Thank you in advance 

[Anonymous · Accepted solution]

Hi @Krish_A ,

Please try the following methods and check if they can solve your problem:

1.Create the simple tables.

2.Create the new column in the Table y.

GainLossY = CALCULATE(AVERAGE('Table x'[gain/loss]), ALL('Table Y'))

3.Create the table visual and select Edit interactions in the format pane. Drag the column into the visual.

4.Create the measure to apply the date filter.

GainLossDate = 
CALCULATE(AVERAGE('Table x'[gain/loss]), ALL('Table y'), 
            DATESBETWEEN('Table x'[Date], 
                DATEADD(LASTDATE('Table x'[Date]), -12, MONTH), 
                LASTDATE('Table x'[Date])
            )
        )

5.Drag the measure into the visual.

Best Regards,

Wisdom Wu

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