cancel
Showing results for
Did you mean:
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
Super User

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

10 REPLIES 10
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Thanks, Ashish,

Expected results:

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

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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!

Frequent Visitor

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?

Super User

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Frequent Visitor

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

Frequent Visitor

Super User

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Frequent Visitor

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)

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

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors