cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RickSchultz
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_IDTest nameScore
1ABC14
1XYZ2
1G15
2ABC2
2XYZ22
3ABC1
3XYZ2
3HHT55

 

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
mahoneypat
Super User
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

Thanks, Ashish,

 

Expected results:

User IDStatus
1"Unhealthy_ABC"
2"Unhealthy_XYZ"
3"Healthy"

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

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
Super User
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

RickSchultz_0-1643155194606.png

 

mahoneypat
Super User
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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)

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

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

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