Predictive analysis using survey results

[jschembre]

I am working with a survey in which each respondent answers multiple times on multiple days.  I'm not sure if my problem would be better solved using a different data model or if it's measures.

I have sample data provided - each person mentions their three favorite colors each day.  I need to be able to see all possible colors a person might like if they like x color on x day.  I will need this to go back and forth, too.  If a person liked x color on the last day, what did they like before, etc.

I've tried measures and have been working to find an "in" type solution similar to SQL, but so far, when I filter down to a specific color/day combination, it's filtering out all the other possiblities.  It's like I need it to filter back up, but can't get that to work quite right.

Here are some screen shots to explan better ("PROVIDED" highlighted yelllow is current format of data before I transform in Power BI):

Expected results (existing table uses (x, x, x) formatUnpivoted data in Power BI

Name	Colors
Joe	(Red, Yellow)
Joe	(Blue, Purple)
Joe	(Yellow, Red)
Dan	(Red, Blue)
Dan	(Blue, Purple)
Dan	(Green, Red)
Mary	(Yellow, Red)
Mary	(Green, Green)
Mary	(Red, Yellow)

Name	day	color
Joe	Monday	Red
Joe	Tuesday	Yellow
Joe	Monday	Blue
Joe	Tuesday	Purple
Joe	Monday	Yellow
Joe	Tuesday	Red
Dan	Monday	Red
Dan	Tuesday	Red
Dan	Monday	Blue
Dan	Tuesday	Purple
Dan	Monday	Green
Dan	Tuesday	Red
Mary	Monday	Yellow
Mary	Tuesday	Red
Mary	Monday	Green
Mary	Tuesday	Green
Mary	Monday	Red
Mary	Tuesday	Yellow

[MFelix · Accepted solution]

Hi @jschembre ,

Create two disconnected tables with the days and the colors then add the following measure:

Preidction =
VAR selectedLines =
    FILTER (
        GROUPBY ( ALLSELECTED ( 'Table' ), 'Table'[color], 'Table'[Name], 'Table'[day] ),
        'Table'[day] = SELECTEDVALUE ( 'Weekday'[WeekDay] )
            && 'Table'[color] = SELECTEDVALUE ( 'Color Slicer'[Color] )
    )
VAR Selectpersons =
    SELECTCOLUMNS ( selectedLines, "Persons", 'Table'[Name] )
RETURN
    COUNTROWS ( FILTER ( 'Table', 'Table'[Name] IN Selectpersons ) )

Result below and in attach PBIX file.

[jschembre]

Thank you so much!