DAX needed to lookup rows and columns from a different table and sum scores

[JamieMcFadden1]

Hi all.  I have a table (SurveyResponse) of scores from a survey.  There is a separate table which assigns each of the responses to a numeric score.  There are actually different numeric scores depending on the "poverty level" measure.  Below is an example of the data in these two tables.

I would like to add calculated columns to the SurveyResponse table, with the total score for each HHID for each poverty level; e.g., 

using the sample data below, the HHID 1 poverty level 1 and 2 respective scores would be:

25 =19+0+6 (poverty level 1)

18 = 12+0+6 (poverty level 2)

In reality, there are ten questions, and 15 poverty levels, so whatever solution I implement will need to "scale up" to that.  Per the sample data below, the survey data responses contain A, B, etc, but the scoring table contains 1A, 1B, 1C, 1D, 2A, 2B, etc.

I hope the question and sample data is clear!

SurveyResponseTable

HHID CollDate P01-Resp P02-Resp P03-Resp

10001	12/15/2022 12:00:00 AM	A	A	A
10002	12/20/2022 12:00:00 AM	B	B	B
10001	1/15/2023 12:00:00 AM	B	A	A

Question	Ans	Response	Score-pov-level1	Score-pov-level2
How many members does the household have?	1A	2 or less	19	12
	1B	3 to 4	11	9
	1C	5 to 6	6	6
	1D	7 or more	0	0
Does every child between the ages of 6 and 12 currently attend preschool or school?	2A	At least one child between the ages of 6 and 12 does not attend preschool or school	0	0
	2B	There are no children between the ages of 6 and 12	8	6
	2C	Every child between the ages of 6 and 12 attends preschool or school	3	3
Has any member of this household reached a higher education level or university?	3A	Yes	6	6
	3B	No	0	0

[JamieMcFadden1]

Please let me know if anyone can give me advice on the JSON question and uploading a model.  Thanks!

In the meantime, I would pose the following question.  I have the tables as shown below.

In the Responses table, I have DAX formulas for the NAT and NEX calculated columns as:

=LOOKUPVALUE(Scoring[NAT],'Scoring'[Q],'Responses'[Q],Scoring[A],'Response'[A])

=LOOKUPVALUE(Scoring[NEX],'Scoring'[Q],'Response'[Q],Scoring[A],'Response'[A])

Is there a way I can reference the column names of NAT and NEX in the DAX formula, rather than hard-coding them?  I will have many more columns like this, and it seems it would be nice to avoid hard-coding the strings NAT, NEX, etc. in each formula.

Responses

HHID	Date	Q	A	NAT	NEX
1001	12/15/2022	1	A	19	12
1001	12/15/2022	2	A	0	0
1001	12/15/2022	3	A	6	6
1002	12/20/2022	1	B	11	9
1002	12/20/2022	2	B	8	6
1002	12/20/2022	3	B	0	0
1001	1/15/2023	1	B	11	9
1001	1/15/2023	2	A	0	0
1001	1/15/2023	3	A	6	6

Scoring

Q	A	NAT	NEX
1	A	19	12
1	B	11	9
1	C	6	6
1	D	0	0
2	A	0	0
2	B	8	6
2	C	3	3
3	A	6	6
3	B	0	0

[JamieMcFadden1]

Hi I have revamped my sample example, including the input tables

I do not know how to convert these tables to JSON, so I will show them below.  If someone explains to me how to convert these to JSON, I can do that for future questions.

I also created a PBIX file.  It would be helpful to upload this, to more clearly ask some questions.  The link above about how to upload it is not very detailed.  I do have OneDrive and I can put the file in a folder there, but I am not sure what to do next.

Let me know, thanks!

Scoring table

QA	NAT	NEX
1A	19	12
1B	11	9
1C	6	6
1D	0	0
2A	0	0
2B	8	6
2C	3	3
3A	6	6
3B	0	0

Response table

HHID	Date	Q1	Q2	Q3
1001	12/15/2022	A	A	A
1002	12/20/2022	B	B	B
1001	1/15/2023	B	A	A

Likelihood table (just a snippet of the table, but it should provide what is needed for the example)

PPI_Key	NAT	NEX
18	61.40%	22.00%
19	59.10%	20.10%
20	56.70%	18.40%
21	54.30%	16.80%
22	51.90%	15.30%
23	49.50%	13.90%
24	47.10%	12.60%
25	44.70%	11.50%

[JamieMcFadden1]

Below is hopefully a better representation of the sample SurveyResponse table:

HHID	CollDate	P01-Resp	P02-Resp	P03-Resp
10001	12/15/2022	A	A	A
10002	12/20/2022	B	B	B

[Anonymous]

Hi @JamieMcFadden1 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Unpivot the columns [P01-Resp], [P02-Resp] and [P03-Resp] of SurveyResponseTable

2. Create two calculated columns as below in the SurveyResponseTable

poverty level 1 = 
CALCULATE (
    SUM ( 'Survey'[Score-pov-level1] ),
    FILTER (
        'Survey',
        VALUE ( LEFT ( 'Survey'[Ans], 1 ) )
            = VALUE ( MID ( 'SurveyResponse'[Response], 2, 2 ) )
            && RIGHT ( 'Survey'[Ans], 1 ) = 'SurveyResponse'[Answer]
    )
)

poverty level 2 = 
CALCULATE (
    SUM ( 'Survey'[Score-pov-level2] ),
    FILTER (
        'Survey',
        VALUE ( LEFT ( 'Survey'[Ans], 1 ) )
            = VALUE ( MID ( 'SurveyResponse'[Response], 2, 2 ) )
            && RIGHT ( 'Survey'[Ans], 1 ) = 'SurveyResponse'[Answer]
    )
)

If the above one can't help you get the expected result, please provide more raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[JamieMcFadden1]

Thanks for your response!

I am going to use what you have built, and try to create a variation of it.  I will probably be back with questions. 

In a nutshell, I want to add up the scores for each HHID/Data combination within another table, rather than in a Matrix/PivotTable visualization.  I need to use the total scores to look up a probability of poverty from another table.  

Thanks for getting me this far!

[Anonymous]

Hi @JamieMcFadden1 ,

I'm not clear about your requirement, could you please provide more details(sample data, specific example or screenshot etc.) on it? You can add these info base on my sample pbix file, then share the file with me. You can refer the following link to upload the file to the community and grant me the sufficient permission to access it. Thank you.

How to upload PBI in Community

Best Regards

Rena

[JamieMcFadden1]

In my work on this, I have created a new table, and I am referring to it in PowerQuery as an Excel table.  I have looked online for a way to convert it to a JSON file, so I can upload a new PBIX file.  Nothing is jumping out to me.  Could you send me perhaps a link as to how to do that?  Thanks!