Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
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
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
No no
Just for two months
Like what all are in Jan are reapeated in March.
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
I have two different slicers for time period
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.
@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],",")
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.