Forum Discussion

SidharthK16's avatar
SidharthK16
Frequent Visitor
3 years ago

Lookupvalue within same column

Hi All,

I have a table listing Respondent Id's and Brand.
2nd table with time period as months .

I want to check the Repeat Rate of Respondent Id's with different Months(Like if in April we have 100 Respondent Id's and in May we have 200 Respondent Id's, So what all Respondent Id's have been repeated in the May month.)

Sharing excel dummy data,
So in excel we can do this with vlookup function .


MonthRepondent IdBrand MonthBrandRepondent IdVlookup
AprilASprite MaySpriteAA
AprilBSprite MaySpriteBB
AprilCSprite MaySpriteCC
AprilDSprite MaySpriteG 
AprilESprite MaySpriteh 
 Count Of Respondent Id5   Count of Repeated Respondent Id's3

 Can you please guide me how can we achieve this in Powerbi?

Need Help
Show and Tell
Tips and Tricks

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    SidharthK16 Use INTERSECT.

    Measure =
  VAR __April = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Month] = "April"),"__ID",[Respondent Id]))
  VAR __May = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Month] = "May"),"__ID",[Respondent Id]))
RETURN
  CONCATENATEX(INTERSECT(__May, __April),[__ID],",")
    • SidharthK16's avatar
      SidharthK16
      Frequent Visitor
      3 years ago

      Thanks Greg for you solution.
      But this is not working for me .

      VAR __April = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Month] = "April"),"__ID",[Respondent Id]))

      I don't want my month to fixed, user can select any month as per their need like June , July and also The respondent Id are in a column but here we have to add the measure at the end.


  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi SidharthK16 ,

    According to your description, you don't want the month to fixed. In my understanding, for each month, you just want to calculate the number of repeated RespondentID comparing with the previous month.

    I create a sample.

    Here I use number to display the month, if you haven't it like me, you can create one or a rank based on year-month is also OK. Here's my solution, create a calculated column.

    Column =
IF (
    COUNTROWS (
        FILTER (
            'Table',
            'Table'[Month]
                = EARLIER ( 'Table'[Month] ) - 1
                && 'Table'[Repondent Id] = EARLIER ( 'Table'[Repondent Id] )
        )
    ) > 0,
    1,
    0
)

    Get the result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • SidharthK16's avatar
      SidharthK16
      Frequent Visitor
      3 years ago

      Thanks for solution!

      But what if I have Multiple months like Jan, Feb ,March, April, May etc.
      And I have to check Repeat rates for Jan and March

       

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Community Support
        3 years ago

        Hi SidharthK16 ,

        Do you mean for each month, you need to check all the previous months? eg for May, need to check repeat with Jan, Feb ,March, April seperately?
        Best Regards,
        Community Support Team _ kalyj