cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
djensen Frequent Visitor
Frequent Visitor

Look up score with filters across tables with transposed data

Hoping this hasn't been asked already. Fairly new to PowerBI/DAX, although I'm an experienced Excel user.

 

I have two tables. For the sake of simplicity, let's say Table A is structured like this:

 

Name1100120013001400
Juana1125
Julie3434
Martha5235
Angie4155
Mary4235

 

Where columns B-E refer to the client's score on a section of the survey she completed, ranging from 1-5.

 

Table B is structured like this:

 

InstitutionCategoryWorkshop
Organization A1100Grow your sales
Organization B1100Sales forecasting
Organization C1200Process analysis
Organization D1300Best practices for hiring
Organization E1300Managing teams
Organization F1300Determining employee compensation
Organization G1400Creating brand awareness
Organization H1400Defining your brand's key messages

 

Where the column "Category" refers to columns B-E in Table A, and the category value can be repeated.

 

A splitter in the report allows the user to filter results by name, so that a report can be generated showing the client's score for each category on the survey.

 

Additionally, I want to create a table that will return the workshops available for the categories in which the woman scored lower on the survey (1 or 2 out of 5). For example, if the user selects Juana, the table would display Organizations A-F; if she selects Martha, the table would only display Organization C; and if she selects Julie, the table will be blank.

 

An added benefit would be the ability to sort the table so that organizations with the lowest category score would appear first, but this is not essential.

 

In Excel, I would create a helper table that uses INDEX-MATCH to return the value for each category for the client the user selected. Then in a second helper table, I would return the score for each workshop and use this to determine whether or not the workshop is displayed.

 

In PowerBI, I'm not sure how to go about this. The most straightforward way seems to be to create a column in Table B with nested IF formulas, for example: "Score=IF(TableB[Category]=1100,AVERAGE(TableA[1100]),IF(TableB[Category]=1200,AVERAGE(TableA[1200],IF..."

 

Then to the table visual I add a filter that "Score" must be equal or less than 2.

 

The problem is that when the user selects a client, "Score" does not change.

 

Any ideas on how to solve this issue/if it can be solved would be much appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator Eric_Zhang
Moderator

Re: Look up score with filters across tables with transposed data


@djensen wrote:

Hoping this hasn't been asked already. Fairly new to PowerBI/DAX, although I'm an experienced Excel user.

 

I have two tables. For the sake of simplicity, let's say Table A is structured like this:

 

Name 1100 1200 1300 1400
Juana 1 1 2 5
Julie 3 4 3 4
Martha 5 2 3 5
Angie 4 1 5 5
Mary 4 2 3 5

 

Where columns B-E refer to the client's score on a section of the survey she completed, ranging from 1-5.

 

Table B is structured like this:

 

Institution Category Workshop
Organization A 1100 Grow your sales
Organization B 1100 Sales forecasting
Organization C 1200 Process analysis
Organization D 1300 Best practices for hiring
Organization E 1300 Managing teams
Organization F 1300 Determining employee compensation
Organization G 1400 Creating brand awareness
Organization H 1400 Defining your brand's key messages

 

Where the column "Category" refers to columns B-E in Table A, and the category value can be repeated.

 

A splitter in the report allows the user to filter results by name, so that a report can be generated showing the client's score for each category on the survey.

 

Additionally, I want to create a table that will return the workshops available for the categories in which the woman scored lower on the survey (1 or 2 out of 5). For example, if the user selects Juana, the table would display Organizations A-F; if she selects Martha, the table would only display Organization C; and if she selects Julie, the table will be blank.

 

An added benefit would be the ability to sort the table so that organizations with the lowest category score would appear first, but this is not essential.

 

In Excel, I would create a helper table that uses INDEX-MATCH to return the value for each category for the client the user selected. Then in a second helper table, I would return the score for each workshop and use this to determine whether or not the workshop is displayed.

 

In PowerBI, I'm not sure how to go about this. The most straightforward way seems to be to create a column in Table B with nested IF formulas, for example: "Score=IF(TableB[Category]=1100,AVERAGE(TableA[1100]),IF(TableB[Category]=1200,AVERAGE(TableA[1200],IF..."

 

Then to the table visual I add a filter that "Score" must be equal or less than 2.

 

The problem is that when the user selects a client, "Score" does not change.

 

Any ideas on how to solve this issue/if it can be solved would be much appreciated.


@djensen

The first step is to re-model table A with a unpivot.

Capture.PNG

 

Then create a measure like

isShown =
IF (
    HASONEVALUE ( TableA[Name] ) && ISFILTERED ( TableA[Name] )
        && CONTAINS (
            FILTER ( ALLSELECTED ( TableA ), TableA[Value] <= 2 ),
            TableA[Attribute], FORMAT ( MAX ( TableB[Category] ), "" )
        ),
    1,
    BLANK ()
)

Capture.PNGCapture.PNGCapture.PNG

 

Check more details in  the attached pbix.

 

 

 

View solution in original post

2 REPLIES 2
Highlighted
Moderator Eric_Zhang
Moderator

Re: Look up score with filters across tables with transposed data


@djensen wrote:

Hoping this hasn't been asked already. Fairly new to PowerBI/DAX, although I'm an experienced Excel user.

 

I have two tables. For the sake of simplicity, let's say Table A is structured like this:

 

Name 1100 1200 1300 1400
Juana 1 1 2 5
Julie 3 4 3 4
Martha 5 2 3 5
Angie 4 1 5 5
Mary 4 2 3 5

 

Where columns B-E refer to the client's score on a section of the survey she completed, ranging from 1-5.

 

Table B is structured like this:

 

Institution Category Workshop
Organization A 1100 Grow your sales
Organization B 1100 Sales forecasting
Organization C 1200 Process analysis
Organization D 1300 Best practices for hiring
Organization E 1300 Managing teams
Organization F 1300 Determining employee compensation
Organization G 1400 Creating brand awareness
Organization H 1400 Defining your brand's key messages

 

Where the column "Category" refers to columns B-E in Table A, and the category value can be repeated.

 

A splitter in the report allows the user to filter results by name, so that a report can be generated showing the client's score for each category on the survey.

 

Additionally, I want to create a table that will return the workshops available for the categories in which the woman scored lower on the survey (1 or 2 out of 5). For example, if the user selects Juana, the table would display Organizations A-F; if she selects Martha, the table would only display Organization C; and if she selects Julie, the table will be blank.

 

An added benefit would be the ability to sort the table so that organizations with the lowest category score would appear first, but this is not essential.

 

In Excel, I would create a helper table that uses INDEX-MATCH to return the value for each category for the client the user selected. Then in a second helper table, I would return the score for each workshop and use this to determine whether or not the workshop is displayed.

 

In PowerBI, I'm not sure how to go about this. The most straightforward way seems to be to create a column in Table B with nested IF formulas, for example: "Score=IF(TableB[Category]=1100,AVERAGE(TableA[1100]),IF(TableB[Category]=1200,AVERAGE(TableA[1200],IF..."

 

Then to the table visual I add a filter that "Score" must be equal or less than 2.

 

The problem is that when the user selects a client, "Score" does not change.

 

Any ideas on how to solve this issue/if it can be solved would be much appreciated.


@djensen

The first step is to re-model table A with a unpivot.

Capture.PNG

 

Then create a measure like

isShown =
IF (
    HASONEVALUE ( TableA[Name] ) && ISFILTERED ( TableA[Name] )
        && CONTAINS (
            FILTER ( ALLSELECTED ( TableA ), TableA[Value] <= 2 ),
            TableA[Attribute], FORMAT ( MAX ( TableB[Category] ), "" )
        ),
    1,
    BLANK ()
)

Capture.PNGCapture.PNGCapture.PNG

 

Check more details in  the attached pbix.

 

 

 

View solution in original post

djensen Frequent Visitor
Frequent Visitor

Re: Look up score with filters across tables with transposed data

Thanks! This was just what I needed!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 156 members 2,106 guests
Please welcome our newest community members: