Help with ALLSELECTED inside Virtual Table

[ankit]

Hi @BrianJ, @AntrikshSharma, Others

While working on solution for below Task, I am facing an issue with using ALLSELECTED inside calculation.I have identified the WorkAround but need to understand the issue. Request your help on this.

Create a Moving Range Measure

Thank you all for your assistance, I’m slowly getting to a resolution on this issue. I have fixed the correct Totals issue The Card Image below, shows that the test 14 mR measure shows as 76.159, really close to the required number of 76.112 The DAX below is how I solved it. test 14 mR = SUMX( FILTER( SUMMARIZE('Contract Review','Contract Review'[Review No]), [test 3 mR] > 0), [test 3 mR]) You don’t want to know how long I messes around with this today, I’ll be …

Requirement- Based on Filter, Need to get values for Last maximum field value of the current Evaluation Context in visual.
In example, I am using a Filter on Dates[MonthnYear], In table visual I need to get [Total Sales] for previous MonthnYear from fitler Selections and for Total values, need to get Sum of [Total Sales] of all the Rows.

Working Solution - I am able to make it work using below measure by creating a separate table using ALLSELECTED outside main expression.

AllSel-SepTab = 
var AllSelectedDates = SUMMARIZE(ALLSELECTED(Dates[MonthnYear]),Dates[MonthnYear])

var TabFinal =
ADDCOLUMNS (
    ADDCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE ( VALUES ( Dates[MonthnYear] ), Dates[MonthnYear] ),
            "maxdate", CALCULATE ( MAX ( Dates[MonthnYear] ) )
        ),
        "prev",
            CALCULATE (
                MAX ( Dates[MonthnYear] ),
                FILTER ( AllSelectedDates, Dates[MonthnYear] < [maxdate] )
            )
    ),
    "Sales", CALCULATE ( [Total Sales], FILTER ( AllSelectedDates, Dates[MonthnYear] = [prev] ) )
)


return
sumx(TabFinal,[sales])

Problem : I am facing issue when I am trying below i.e. using ALLSELECTED inside main Filter expression. Here for individual Rows I am getting blank.

AllSel-InsideCalc = 
//var AllSelectedDates = SUMMARIZE(ALLSELECTED(Dates[MonthnYear]),Dates[MonthnYear])

var TabFinal =
ADDCOLUMNS (
    ADDCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE ( VALUES ( Dates[MonthnYear] ), Dates[MonthnYear] ),
            "maxdate", CALCULATE ( MAX ( Dates[MonthnYear] ) )
        ),
        "prev",
            CALCULATE (
                MAX ( Dates[MonthnYear] ),
                FILTER ( ALLSELECTED(Dates[MonthnYear]), Dates[MonthnYear] < [maxdate] )
            )
    ),
    "Sales", CALCULATE ( [Total Sales], FILTER ( ALLSELECTED(Dates[MonthnYear]), Dates[MonthnYear] = [prev] ) )
)

return
sumx(TabFinal,[sales])

I have also tried with ALL function and it is working in both the scenarios. Can check measure All-SepTab, All-InsideCalc as shown in diagram also.

Can you pls suggest why ALLSELECTED inside Filter condition is not working. Attached the PBIX.AllSelected Help.pbix (394.8 KB)

Thanks
Ankit J

[AntrikshSharma]

@ankit When using ITERATORS there are always two context, regular filter context (under which everything works as expected ) and a shadow filter context. Shadow filter context are dormant they don’t do anything at all unless ALLSELECTED calls them and that is the real way in which ALLSELECTED operates, when we think what we have selected in slicer is what ALLSELECTED picks then that’s not what is happening.

PBI generates queries in terms of SUMMARIZECOLUMNS and it iterates for each cell of the visual and creates a shadow filter context that ALLSELECTED uses, that’s how a regular measure like this works:

=
CALCULATE ( [Total Sales], ALLSELECTED ( Dates[Calendar Year] ) )

When you use an ITERATOR in your measure and then call ALLSELECTED inside it then ALLSELECTED uses the most recent shadow filter context created by that ITERATOR and not by SUMMARIZECOLUMNS, hence confusing the user and almost every time returning unexpected/incorrect result

So basically what you get is 1 shadow filter context created by SUMMARIZECOLUMNS, 1 shadow filter context create by ADDCOLUMNS, but ALLSELECTED can only restore the most nearest shadow filter context, hence it uses the one created by your iterator.

And In your case you have created shadow filter context with SUMMARIZE ( VALUES ( Dates[MonthnYear] ), Dates[MonthnYear] ), also you could just use VALUES ( Dates[MonthnYear] )

ALLSELECTED restores Shadow filter context created by an ITERATOR over the same column.
And ALLSELECTED is restoring Shadow filter context of VALUES () but VALUES () returns only 1 row in the current filter context, and then you are trying to evaluate dates that are less than [MaxDate] temp column therefore you get blank

And when you use ALL then ALLSELECTED restores all the MonthNYear

Measure = 
CALCULATE ( [Total Sales], ALL ( Dates[MonthnYear] ) )

But it works in case of variable because when ALLSELECTED was evaluated the only shadow filter context was coming from SUMMARIZECOLUMNS with 4 dates that’s why it works.

Learn more about this behaviour from here:
https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/

[ankit]

Hi @AntrikshSharma

And I thought I knew DAX Should have gone through SQLBI article earlier. Thanks for the detailed explanation. Will go through this and SQLBI article tomorrow and may come back with additional question.

Thanks,
Ankit J

[AntrikshSharma]

@ankit Sure!

[BrianJ]

@ankit

And I thought I knew DAX

I know, right? If the Definitive Guide to DAX came to life and could answer questions, it would be @AntrikshSharma.

Awesome explanation of what I think is the most confusing concept in DAX.

• Brian

[AntrikshSharma]

@BrianJ haha!! Thanks

[EnterpriseDNA]

Hi @ankit, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!