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

 

 

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

2 Replies

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