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

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.

6 Replies

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