Forum Discussion

Chiniminiz's avatar
Chiniminiz
Frequent Visitor
2 years ago
Solved

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 co...
Need Help
  • Chiniminiz's avatar
    Chiniminiz
    2 years ago

    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! 🙂 
grazitti_sapna's avatar
grazitti_sapna
Icon for Super User rankSuper User
2 years ago

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's avatar
Chiniminiz
Frequent Visitor
2 years ago

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