Forum Discussion

jschembre's avatar
jschembre
Regular Visitor
4 years ago
Solved

Predictive analysis using survey results

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

Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    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.

     

     

     

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    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.