Look up score with filters across tables with transposed data

[djensen]

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.

[Eric_Zhang · Accepted solution]

---

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

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 ()
)

Check more details in  the attached pbix.

[djensen]

Thanks! This was just what I needed!