Forum Discussion

lcfaria's avatar
lcfaria
Helper II
2 years ago
Solved

ALL function for variable table

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]...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    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.

AlexisOlson's avatar
AlexisOlson
Super User
2 years ago

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's avatar
    lcfaria
    Helper II
    2 years ago

    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

    InfoRank
    Info 11
    Info 21
    My info Needed1

     

    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

    InfoRank
    Info 13
    Info 23

     

    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's avatar
      AlexisOlson
      Super User
      2 years ago

      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]
	)