make table unaffected by slicer except one column

[Anonymous]

Hi all,

is it possible to have a table (execpt one column ) completely unaffected by the slicers on a page?

I have a table with two columns 1.a list of products 2. the sales for those products. 

I want to add a third column called 'sales for selected period' 

I want the list of products and the sales columns to remain unaffected when I use a date slicer but I want the sales for selected period column to change.

is this possible?

[Greg_Deckler]

@Anonymous - Yes, don't have your slicer connected to your visual. (Edit Interactions) Create a measure for the one value in your visual that grabs the value(s) in the slicer and returns a result accordingly.

[Anonymous]

@Greg_Deckler  I am using a relative date slicer, what would be the best approach to create the meaure? If has one value?

thank you!

[Greg_Deckler]

@Anonymous - It's really hard to say. You should be able to do something like:

Measure = 

  VAR __SlicerValues = 'SlicerTable'[SlicerColumn]

RETURN

 ...

Once you have the values, you can get the min or max of those and filter by these min's and max's or you might use IN operator. Hard to say without knowing your data and what you are trying to calculate.

[Anonymous]

@Greg_Deckler  thanks for the reply.

I am just trying calculate the distinct count for a column for the period selected in the relative slicer. 

so now I have a table with data and a relative slicer that is not interacting with the table (turned off the interaction).

Now I need to create the measure to do a distinct count of a column called [TransactionID] by the [CustomerID]. 

then when I put the measure in the table i need the relative slicer to interact with only the created measure 

does that make a little more sense ?

[Greg_Deckler]

OK, so probably something along the lines of:

Measure =
  VAR __MaxSlicer = MAX('Slicer'[Column])
  VAR __MinSlicer = MIN('Slicer'[Column])
RETURN
  COUNTX(DISTINCT(SELECTCOLUMNS(FILTER('Table',[Date] >= __MinSlicer && [Date] <= __MaxSlicer),"TransactionID",[TransactionID])),[TransactionID])

[Anonymous]

@Greg_Deckler  this returns the correct value when i put it into a card visual. but when i add it to the table the value reverts back to the unfiltered value. Thank you for your efforts its greatly appreciated

[Greg_Deckler]

@Anonymous - Hmmm, thinking about this, you may have to reverse the logic. Make the slicer affect the visual. Write a measure for each column that you don't want to be affected by the slicer that uses ALL or ALLEXCEPT for example.

I think the reason is that once the slicer does not affect the visual, any measure executing within the context of the visual loses all context for the slicer. So, from the context of the visual, the slicer is always "all" in effect. Painful.

[Anonymous]

@Greg_Deckler I cannot all or allexecpt to work, how would you suggest writting it for a category column?

[Anonymous]

got it, wow i dont know how i missed that . thank you

[amitchandak · Accepted solution]

@Anonymous , Can you use interaction instead.

https://docs.microsoft.com/en-us/power-bi/create-reports/service-reports-visual-interactions

[Anonymous]

@amitchandak right, but I still have a column in the table I want to change when I use the date slicer