Use Column from ADDCOLUMNS in CALCULATE filtercontext

[Chiniminiz]

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 )

[grazitti_sapna]

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 

[Chiniminiz · 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. 

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! 🙂