Lookupvalue within same column

[SidharthK16]

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 .

Month	Repondent Id	Brand		Month	Brand	Repondent Id	Vlookup
April	A	Sprite		May	Sprite	A	A
April	B	Sprite		May	Sprite	B	B
April	C	Sprite		May	Sprite	C	C
April	D	Sprite		May	Sprite	G
April	E	Sprite		May	Sprite	h
	Count Of Respondent Id	5				Count of Repeated Respondent Id's	3

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

[v-yanjiang-msft]

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]

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]

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

[SidharthK16]

No no
Just for two months
Like what all are in Jan are reapeated in March.

[v-yanjiang-msft]

Hi @SidharthK16 ,

What's the logic which two months are compared, if you don't want the formula to be fixed.

Best Regards,
Community Support Team _ kalyj

[SidharthK16]

I have two different slicers for time period

[v-yanjiang-msft]

Hi @SidharthK16 ,

If all time period are in the same column, and we put the column in two slicers. We can't select different values in the two slicers unless we remove the interaction. But if we remove interaction, we can't pick the value selected in the slicer.

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.

[Greg_Deckler]

@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]

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.