SELECTEDVALUE and ALLEXCEPT in table with multiple identical columns

[Anonymous]

I have a table with multiple columns that all have identical values.  (For the purposes of this example I'm using four columns, but in practice there could hundreds.)

I have a slicer for each column in the table.  Interactions between all of these slicers are turned off and each is single-select.  I need to capture the selected value from a single slicer in order to use it in other measures in my report.

This is the measure I have to capture the slicer selection for [Item 1].  It is in a separate table that is not connected to the Items table.

Item 1 Selection =
CALCULATE (
    SELECTEDVALUE ( Items[Item 1] ),
    ALLEXCEPT ( Items, Items[Item 1] )
)

It seems like [Item 1 Selection] should capture "Option A" as the selection for [Item 1], clearing the filters on [Item 2], [Item 3], and [Item 4].  However, it returns (Blank) instead.  Despite using ALLEXCEPT it seems that the filters on [Item 2], [Item 3], and [Item 4] are being retained.

I know that SELECTEDVALUE will return (Blank) both if no values are selected or if more than one value is selected.  To test which of these was the case I created [Item 1 List] to see if multiple values were being selected:

Item 1 List =
CONCATENATEX (
    CALCULATETABLE (
        ALLSELECTED ( Items[Item 1] ),
        ALLEXCEPT ( Items, Items[Item 1] )
    ),
    Items[Item 1],
    ", "
)

[Item 1 List] also returned (Blank), although, if I set all the slicers to "Option A" it returned "Option A":

How to I get [Item 1 Selection] to return "Option A" when that is selected in the [Item 1] slicer, regardless of the other slicer selections?  I had originally approached this problem by placing each column of the Items table in its own separate table disconnected from any other, but the potentially very large number of Items makes this unfeasible in practice.  Hundreds of tables are much more cumbersome than hundreds of columns in a single table.  It seems like ALLEXCEPT is not working properly with SELECTEDVALUE.

I searched for people experiencing similar problems and this was the closest I could find: filter a column with same value as selected value in slicer.  However, this person wants the identical columns to filter each other and I want the opposite to happen.

Thank you for any insight you can give on this problem!

[OwenAuger · Accepted solution]

Hi @Anonymous 

The issue you're seeing here is (in my view) a confusing and annoying one, but is apparently by design as part of the "auto-exist" behaviour. In effect, it means that in certain cases, removing filters with ALL or ALLEXCEPT doesn't appear to work as intended when cross-filtering between columns of the same table.

Behind the scenes, the DAX query that is generated for the "Item 1 selection" card applies the four Item filters simultaneously, resulting in an "empty" filter context (i.e. there are no rows of Items remaining in this filter context). It then evaluates the [Item 1 Selection] measure in that context which unfortunately is a blank result.

See this article for an explanation:

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

It was also discussed a while back in this thread where Marco Russo also provided some comments.

https://community.powerbi.com/t5/Desktop/Suspected-BUG-when-using-ALL-with-multiple-non-ignored-slicer/m-p/506209

The only solution I can suggest is creating an additional related table for Item 1, and apply the Item 1 filter on that table rather than your main table. This would only make sense if Item 1 is the only filter where you need to capture the slicer selection.

Otherwise, we might need to consider some other workarounds or redesign of the data model.

Could you describe a bit more the purpose of these slicers and how the overall report works?

Regards,

Owen

[avatorl]

@Anonymous Did you find a solution? Or is creating multiple tables the only way?

[Anonymous · Accepted solution]

I case anyone is looking for a solution that doesn't involve creating a separate slicer for each column needed, here is what I ended up doing.  I embedded a PowerApp within the PowerBI report which connects to a SQL table with a row for each item and a column for each attribute.  (There is only one attribute in the example given here.)  That SQL table is one of the data sources in the PowerBI report, using DirectQuery.  (This will not work if it is connected in Import mode.)  The user can choose the option for each item in the PowerApp, which then writes these choices back to the SQL table and automatically refreshes the PowerBI report.  It is important to note that at this time only PowerApps you create directly from PowerBI will be able to refresh the report; this cannot be done when importing an already-existing Power App into the report.  This capability might be added to PowerApps at a later date, but it's not available now (August 2019.)

[OwenAuger · Accepted solution]

Hi @Anonymous 

The issue you're seeing here is (in my view) a confusing and annoying one, but is apparently by design as part of the "auto-exist" behaviour. In effect, it means that in certain cases, removing filters with ALL or ALLEXCEPT doesn't appear to work as intended when cross-filtering between columns of the same table.

Behind the scenes, the DAX query that is generated for the "Item 1 selection" card applies the four Item filters simultaneously, resulting in an "empty" filter context (i.e. there are no rows of Items remaining in this filter context). It then evaluates the [Item 1 Selection] measure in that context which unfortunately is a blank result.

See this article for an explanation:

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

It was also discussed a while back in this thread where Marco Russo also provided some comments.

https://community.powerbi.com/t5/Desktop/Suspected-BUG-when-using-ALL-with-multiple-non-ignored-slicer/m-p/506209

The only solution I can suggest is creating an additional related table for Item 1, and apply the Item 1 filter on that table rather than your main table. This would only make sense if Item 1 is the only filter where you need to capture the slicer selection.

Otherwise, we might need to consider some other workarounds or redesign of the data model.

Could you describe a bit more the purpose of these slicers and how the overall report works?

Regards,

Owen

[Anonymous]

Thanks for your quick response @OwenAuger!  I had never encountered "auto-exist" before, but it does seem to be the cause of my problem.  Unfortunately, I need to capture slicer selections for all the items, which could number in the hundreds in the final report.

I want to capture slicer selections for a number of different attributes (Options A-D in this example) for up to 100 different items.  (For the sake of clarity I limited my example to one attribute for four items.)  So in the final report Item 1 might actually have 4 or 5 different slicers for which I need to capture selections, likewise for each other item.  I then use the slicer selections to create measures performing numerical calculations for the selected options for each item.  There is a separate measure for Item 1, Item 2, &c.  These are created in an unrelated table which looks something like this:

Because of the potentially very large number of items I don't think it's feasible to create a separate table for each item.  It works in the example but isn't scaleable for the actual needs of the report.

Does this give a good enough explanation of how my report works?  I'm trying to create examples with fake data that avoid a lot of extraneous complication. 🙂  

I really appreciate your help on this!  If I can't get this to work then I may try to do calculations in R and then pass the results back to Power BI.