Forum Discussion
ALL function for variable table
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 herehttps://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 _RankThe key part is that you need to include both data[Info] and data[Location Order] when using ALL or ALLSELECTED.
Hey AlexisOlson unfortunately, it didn't work. The results are the same.
I tried to replicate it in this sample file, hope it help.
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
_RankThe key part is that you need to include both data[Info] and data[Location Order] when using ALL or ALLSELECTED.
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.