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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
Chiniminiz
Frequent Visitor

Use Column from ADDCOLUMNS in CALCULATE filtercontext

Hi together,
We have a table with ID's, Service, Value. Now we only want to get two things:
- The Service from Rank number 7
- The Values for Service number 7
We created the following short code
 
Unfortunately there is an error in the last line, because the column _RankingTable[Rank] could not be find.
 
Is there a solution for my problem or another way to get the informations needed?
 
Thanks in advance!
 

 

 

Rank 7 Service = 
VAR _Services =
    VALUES ( 'Ergebnisse DAK & IKK'[Service] )
    
VAR _RankingTable =
    ADDCOLUMNS ( _Services, "Rank", RANKX ( _Services, [Anzahl Antworten], ,DESC ) )
    
RETURN
    CALCULATE ( _RankingTable, _RankingTable[Rank] = 7 )

 

 

1 ACCEPTED SOLUTION

Hej grazitti_sapna,

 

unfortunately it doesn't work.

 

In the Screenshot you'll see, that the VAR _ServiceAtRank7 = doesn't recognize the _RankingTable[Rank] from the VAR _RankingTable. 

Chiniminiz_0-1701779616984.png

 

But I've found a solution myself. 

 

Service #7 DisplayValue =
VAR _Nth = 7

VAR _ReferenceService = ALLSELECTED('Ergebnisse DAK & IKK'[Service])

VAR _ServiceAndTickets =
    ADDCOLUMNS(
        VALUES( 'Ergebnisse DAK & IKK'[Service] ),
        "Rank", RANKX( _ReferenceService, [Anzahl Antworten])
    )

VAR _FilterNthProduct =
    FILTER( _ServiceAndTickets, [Rank] = _Nth )

RETURN
CALCULATE(
    [DisplayValue],
    FILTER(
        'Ergebnisse DAK & IKK',
        'Ergebnisse DAK & IKK'[Service] = _NthService
    )
)
 
This works very fine and the main difference is only, that the filter function is stored in a different VAR. This is the clue behind.
 
But thanks for your fast reply! 🙂 

View solution in original post

2 REPLIES 2
grazitti_sapna
Super User
Super User

Hi @Chiniminiz ,


Please try using:-

 

Rank 7 Service =
VAR _Services =
VALUES ( 'Ergebnisse DAK & IKK'[Service] )

VAR _RankingTable =
ADDCOLUMNS ( _Services, "Rank", RANKX ( _Services, [Anzahl Antworten], ,DESC ) )

VAR _ServiceAtRank7 =
CALCULATETABLE (
_RankingTable,
FILTER ( _RankingTable, _RankingTable[Rank] = 7 )
)

VAR _Service7Values =
CALCULATE (
VALUES ( 'Ergebnisse DAK & IKK'[Value] ),
'Ergebnisse DAK & IKK'[Service] IN SELECTCOLUMNS ( _ServiceAtRank7, "Service", 'Ergebnisse DAK & IKK'[Service] )
)

RETURN
_Service7Values

 

 

And please let me know if it works.

Thank you 

Hej grazitti_sapna,

 

unfortunately it doesn't work.

 

In the Screenshot you'll see, that the VAR _ServiceAtRank7 = doesn't recognize the _RankingTable[Rank] from the VAR _RankingTable. 

Chiniminiz_0-1701779616984.png

 

But I've found a solution myself. 

 

Service #7 DisplayValue =
VAR _Nth = 7

VAR _ReferenceService = ALLSELECTED('Ergebnisse DAK & IKK'[Service])

VAR _ServiceAndTickets =
    ADDCOLUMNS(
        VALUES( 'Ergebnisse DAK & IKK'[Service] ),
        "Rank", RANKX( _ReferenceService, [Anzahl Antworten])
    )

VAR _FilterNthProduct =
    FILTER( _ServiceAndTickets, [Rank] = _Nth )

RETURN
CALCULATE(
    [DisplayValue],
    FILTER(
        'Ergebnisse DAK & IKK',
        'Ergebnisse DAK & IKK'[Service] = _NthService
    )
)
 
This works very fine and the main difference is only, that the filter function is stored in a different VAR. This is the clue behind.
 
But thanks for your fast reply! 🙂 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.