VALUES() for a slicer ignoring other slicers

[Anonymous]

Hello everyone. 

My objective is to be able to use different slicers to control the result of a measure for each month independently. I am going to present a simplyfied version of the problem, but solving this will surely solve the bigger task.

I have a table called "Slicers" as this:

I want to use the selection of indepent slicers for each column

I make a measure called "_test":

This initially seems to work, as can be seen in this image:

However, if I select something in the second slicer (different than the value in the first slicer), the value turns BLANK():

The only exception is the selection A,B, which are the only two different values that coexist in the table (see figure 1):

So my general idea is that the measure itself is worded correctly, but the "intersection" of the slicers creating a null result causes some sort of issue I haven't been able to solve. So far if tried with ALL(), ALLEXCEPT(), REMOVEFILTERS(), KEEPFILTERS(), FILTER(), FILTERS() and different combinations, but to no avail.

Could someone shed some light on how to tackle this? I know a possibility would be to create different slicer tables, but we would need 12 (one for each month), and I would like to find an alternative, since I'm sure there must be a way.

Thank you in advance,

Alberto.

[v-stephen-msft]

Hi @Anonymous ,

My question is that when you select A in the Version_January slicer, there is only A for you to choose in the Version_February slicer, why do you have D appearing, is it because you put fields in other tables?

My suggestion is that using the SELECTEDVALUE function should not be affected by other slicers.

Best Regards,

Stephen Tao

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

[Anonymous]

Hi Stephen.

That's because the interaction between the slicers is disabled, I should have mentioned it:

As for SELECTEDVALUE, I encounter the very same problem:

[johnt75 · Accepted solution]

This is really interesting, the problem is because of the difference between how CALCULATE and SUMMARIZECOLUMNS work with filter arguments. Behind the scenes Power BI is translating your card visual into this DAX query

 DEFINE
  VAR __DS0FilterTable = 
    TREATAS({"A"}, 't1'[Version_January])

  VAR __DS0FilterTable2 = 
    TREATAS({"C"}, 't1'[Version_February])

EVALUATE 
  SUMMARIZECOLUMNS(__DS0FilterTable, __DS0FilterTable2, "v_test", IGNORE('t1'[_test]))

which indeed returns BLANK as the result.

However, passing the same filters into CALCULATE as in 

 CALCULATE( [_test], __DS0FilterTable, __DS0FilterTable2 )

produces the correct result of A, which shows that your measure definition is correct.

Given that your problem is caused by the inner workings of Power BI, and there is no way to change that, I don't think you have any option other than to split your slicers into separate, unconnected tables.