ALL function for variable table

[lcfaria]

Hi everyone,

I need to use the ALL function for a variable table that is calculating rankings.

This is what I am doing:

VAR _Table1 = FILTER(ALLSELECTED(Table1[Info]), NOT(ISBLANK([Measure])))

VAR _Table2_Forced_Selection = FILTER(ALL(Table1[Info]), Table1[Info] = "My info needed")

VAR _Table_With_Forced_Selection =  

    DISTINCT(

        UNION(

            _Table1,

            _Table2_Forced_Selection

        )

    )

VAR _AllTableRanking =

    ADDCOLUMNS(

        _Table_With_Forced_Selection ,

        "Rank", RANKX(_Table_With_Forced_Selection, [Measure], , DESC, Dense)

    )

VAR _Rank =

    MAXX(

        FILTER(_AllTableRanking, [Info] = "My info needed"),

        [Rank]

    )

I need to use the ALL function within MAXX in my variable table "_AllTableRanking".

However, the ALL function does not accept a variable table.

Is there any way to get the same result?

[AlexisOlson]

How would you expect
FILTER(_AllTableRanking, [Info] = "My info needed")
to behave differently than

FILTER(ALL(_AllTableRanking), [Info] = "My info needed")

if that were valid syntax?

[lcfaria]

Hi @AlexisOlson first of all, thanks for checking out my topic.

Consider a scenario where I select three values from a slicer, Info 1 and Info 2 and "My info needed". With "My info needed" included in the selection, the ranking works correctly.

For example, with Info 1, Info 2 and My info Needed selected, the correct result in a table view is:

Info Rank

Info	Rank
Info 1	1
Info 2	1
My info Needed	1

The rank is always 1, because I am forcing it here:

FILTER(_AllTableRanking, [Info] = "My info needed")

However, if I remove "My info needed" from the slicer, the ranking changes to Rank 3, but I need it to stay at Rank 1.

Info Rank

Info	Rank
Info 1	3
Info 2	3

Even forcing it here:

VAR _Table2_Forced_Selection = FILTER(ALL(Table1[Info]), Table1[Info] = "My info needed")

VAR _Table_With_Forced_Selection =  

    DISTINCT(

        UNION(

            _Table1,

            _Table2_Forced_Selection

        )

    )

I cannot remove the outside context.

Not sure if I managed to explain the topic well. I hope so.

[AlexisOlson]

Hmm. This is a bit tricky without having your pbix to test against.

Does it help if you break up the measure calculation and the RANKX?

VAR _Table1 = FILTER ( ALLSELECTED ( Table1[Info] ), NOT ( ISBLANK ( [Measure] ) ) )
VAR _Table2_Forced_Selection = FILTER ( ALL ( Table1[Info] ), Table1[Info] = "My info needed" )
VAR _Table_With_Forced_Selection = DISTINCT ( UNION ( _Table1, _Table2_Forced_Selection ) )
VAR _Table_With_Measure = ADDCOLUMNS ( _Table_With_Forced_Selection, "@Measure", [Measure] )
VAR _AllTableRanking =
    ADDCOLUMNS (
        _Table_With_Measure,
        "Rank", RANKX ( _Table_With_Measure, [@Measure], , DESC, DENSE )
    )
VAR _Rank =
    MAXX (
		FILTER ( _AllTableRanking, [Info] = "My info needed" ),
		[Rank]
	)

[lcfaria]

Hey @AlexisOlson  unfortunately, it didn't work. The results are the same.

I tried to replicate it in this sample file, hope it help.

Link: ALL for variable table.pbix

[AlexisOlson · Accepted solution]

I think what's tripping you up is the column you are sorting data[Info] by isn't getting cleared out.

I've written about this before here

https://www.linkedin.com/feed/update/urn:li:linkedInArticle:7023407171707015168/

The SQLBI guys wrote about it first.
https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/

I'm not sure exactly if this is what you're after but it might be a good starting place:

VAR _MyInfoNeeded = "My info needed"
VAR _Avg_MyInfoNeeded = [Avg $ My info needed only]
VAR _AllSelectedInfos_ = ALLSELECTED ( data[Info] )
VAR _AllRelevantInfos_ =
    FILTER (
        ALL ( data[Info], data[Location Order] ),
        data[Info] IN _AllSelectedInfos_ || data[Info] = _MyInfoNeeded
    )
VAR _Avgs_ = ADDCOLUMNS ( _AllRelevantInfos_, "@Avg", [Average $] )
VAR _Rank = RANKX ( _Avgs_, [@Avg], _Avg_MyInfoNeeded, ASC, DENSE )
RETURN
    _Rank

The key part is that you need to include both data[Info] and data[Location Order] when using ALL or ALLSELECTED.

[lcfaria]

Hey @AlexisOlson, thanks a lot for your help! The solution you provided was just what I needed. Your article and the article from SQLBI you shared were really helpful and gave me great insights. I appreciate your time and effort in helping me out.