Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
lcfaria
Helper II
Helper II

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?

1 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.

View solution in original post

6 REPLIES 6
AlexisOlson
Super User
Super User

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?

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.

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

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

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.

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.