Calculated column which gives one value in the One table for multiple values & rows on the Many tabl

[RickSchultz]

Hi!  I hope this doesn't sound too confusing - first time posting.

I have two tables.

The first table is a User table, with each user unique, identified by user_id.  That's the ONE table.

The other table has medical test results.  Each row is unique for the user and test.  That's the MANY table

I want a column in the User table which tells me how the User did on two specific tests (there are 10 total tests, so a simple CONCATENATEX doesn't work).  So:

MANY table:

User_ID	Test name	Score
1	ABC	14
1	XYZ	2
1	G	15
2	ABC	2
2	XYZ	22
3	ABC	1
3	XYZ	2
3	HHT	55

The only tests that matter are ABC and XYZ (in the example, each user has a score for each test).

Desired outcomes:

If the person scores under 4 for both tests, I want return "Healthy"

If the person scores under 4 for one test and above 3 for the other, I want to return "Unhealthy - Test ____"

If the person scores abover 3 for both tests, I want to return "Both"

The data model has a one-to-many link between the two tables.

Thank you in advance for taking a look at this!

[Ashish_Mathur]

Hi,

Based on the sample data that you have shared, show the expected result as well.

[RickSchultz]

Thanks, Ashish,

Expected results:

User ID	Status
1	"Unhealthy_ABC"
2	"Unhealthy_XYZ"
3	"Healthy"

[Ashish_Mathur]

Hi,

Try these measures:

Scores = SUM(Data[Score])

ABC score = CALCULATE([Scores],Data[Test name]="ABC")

XYZ score = CALCULATE([Scores],Data[Test name]="XYZ")

Measure 2 = if(HASONEVALUE(Data[Test name]),[Scores],if([ABC score]<4,if([XYZ score]<4,"Healthy","Unhealthy - test XYZ"),if([XYZ score]<=4,"Unhealthy - test ABC","Both")))

Hope this helps.

[RickSchultz]

Hi Ashish, and thanks again for your help.  Unfortunately, the second formula threw an error - it was looking for a single value from the "Scores" calculation, but said there wasn't a single value.  I'd give more background if I understood the progression of your answer better.

That said, the approach you both provided was correct; I needed to first get values for the test scores into a query, then aggregate those values into my desired structure.  I was able to effect that in my original SQL query by referencing the "test" table twice and using that "query" as a subquery in my main user table.  
As he provided the first answer, and got me thinking in the right direction, I'm giving Pat the Solution kudos.  Thank you both!

[RickSchultz]

Thank, Ashish!  So, just so I understand, you're suggesting 3 supporting measures, then one measure to bring them all together, correct?  I'll give that a try, but I'm curious - how does that overcome the many-to-one issue that seemed to crop up when using this approach with variables (as Pat did)?  Is that the reason for the HASONEVALUE function?

[mahoneypat]

Not sure what that error means without learning more about your model. Can you post a screenshot of your model/diagram view page that shows the two tables and their relationship? Also, have you confirmed the data type of the id columns are the same (e.g., whole number, text)?

Pat

[RickSchultz]

BTW, forgot to say - yes, both user_id fields are whole numbers.

[RickSchultz]

[mahoneypat · Accepted solution]

Here is a column expression that should work.

NewColumn =
VAR abcresult =
    CALCULATE ( MIN ( Results[Score] ), Results[Test name] = "ABC" ) > 3
VAR xyzresult =
    CALCULATE ( MIN ( Results[Score] ), Results[Test name] = "XYZ" ) > 3
VAR result =
    SWITCH (
        TRUE (),
        abcresult && xyzresult, "Both",
        abcresult && NOT ( xyzresult ), "Unhealthy_Test XYZ",
        NOT ( abcresult ) && xyzresult, "Unhealthy_Test ABC",
        NOT ( abcresult ) && NOT ( xyzresult ), "Healthy"
    )
RETURN
    result

Pat

[RickSchultz]

Thanks for the quick reply, Pat!  I'm getting an error message which suggests the relationship may be a problem (first line of the error message):

============================
Error Message:
============================

An unexpected error occurred (file 'xmvsquery.cpp', line 13960, function 'XMVSXJoin::BuildRelationships').
The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
The current operation was cancelled because another operation in the transaction failed.

----------------------------
An unexpected error occurred (file 'xmvsquery.cpp', line 13960, function 'XMVSXJoin::BuildRelationships').
The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
The current operation was cancelled because another operation in the transaction failed.

============================
Call Stack:
============================

at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ProcessResults(XmlaResultCollection results)
at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteCaptureLogAndProcessResults(OperationType type, Boolean cancellable, Boolean raw, Boolean raiseEvents)
at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
----------------------------
at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
at Microsoft.AnalysisServices.Common.SandboxEditor.ChangeFormula(TableWidgetPanel currentTable, IList`1 colIndices, IList`1 names, IList`1 expressions, Boolean doFormulaBarCommit, IList`1 displayIndices)

============================