Forum Discussion

lcfaria's avatar
lcfaria
Icon for Helper II rankHelper 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
Icon for Super User rankSuper 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]
	)
lcfaria's avatar
lcfaria
Icon for Helper II rankHelper II
2 years ago

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's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    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.

    • lcfaria's avatar
      lcfaria
      Icon for Helper II rankHelper II
      2 years ago

      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.