Measures and Calculated Columns that Look at Multiple Unique Identifiers

[JamesBockett]

Hi Community!

I have three tables of data:

1) Enquiries - has fields of Enquiry ID, Case ID, In Scope, Priority

2) Cases - has fields of Case ID, Case Type, Status (Cases are enquiries that have been 'converted' to an accepted case)
3) Vehicle Profiles - has fields of Vehicle Profile ID, Enquiry ID, Case ID, Vehicle Type, Vehicle Colour

Enquiries are linked to cases via the Case ID, using Many to one relationship (cross filter direction = both). Vehicle Profiles are linked to Enquiries via the Enquiry ID, using a one to many relationship (cross filter direction = both).

What I would like to do is create a calculated column in the cases table that says whether a case was mandatory or discretionary. So far, I have written the following in the cases table:

Mandatory Case =

VAR In_scope = LOOKUPVALUE( Enquiries[In scope] , 'Enquiries'[Case Id.] , 'Cases'[Case Id.])

VAR Priority = LOOKUPVALUE( Enquiries[Priority] , Enquiries[Case Id.] , 'Cases'[Case Id.] )

VAR Vehicle_Type = LOOKUPVALUE( 'Vehicle Profiles'[Vehicle Type] , 'Vehicle Profiles'[Case Id.] , 'Cases'[Case Id.] )

RETURN

IF(

    In_scope IN { "Yes" } &&

    Priority IN { "Very High" } &&

    'Cases'[Case Type] IN { "In-House" } &&

    Vehicle_Type IN { "Hatchback" } ,

    "Mandatory Case" ,

    "Discretionary"

)

I get the error: 'A table of multiple values was supplied where a single value was expected'. I assume this is because there can be more than one vehicle profile per Case ID.

My question is, how do I get around this? If any of the vehicle types on a case are hatchback, the case becomes mandatory.

Many thanks, in advance!

Enquiries Example

Case Example

Vehicle Profiles Example

[v-yueyunzh-msft]

Hi , @JamesBockett 

I test in my side and reproduce your error . This caused by the

"VAR Vehicle_Type = LOOKUPVALUE( 'Vehicle Profiles'[Vehicle Type] , 'Vehicle Profiles'[Case Id.] , 'Cases'[Case Id.] )"

Because lookupvalue cannot return cases where there are multiple matches.

You can try to use this dax to test:

Mandatory Case =
VAR In_scope = LOOKUPVALUE( Enquiries[In scope] , 'Enquiries'[Case Id.] , 'Cases'[Case Id.])
VAR Priority = LOOKUPVALUE( Enquiries[Priority] , Enquiries[Case Id.] , 'Cases'[Case Id.] )
VAR Vehicle_Type = SELECTCOLUMNS(FILTER('Vehicle Profiles','Vehicle Profiles'[Case Id.]=EARLIER('Cases'[Case Id.])),"1",'Vehicle Profiles'[Vehicle Type])
RETURN
IF(
    In_scope IN { "Yes" } &&
    Priority IN { "Very High" } &&
    'Cases'[Case Type] IN { "In-House" } &&
     {"Hatchback"} in Vehicle_Type ,
    "Mandatory Case" ,
    "Discretionary"
)

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[JamesBockett]

Hi @v-yueyunzh-msft 

Thank you for your reply. 

Your DAX does stop the error ppering but, when I filter by the new colum (Mandatory Case) everything is showing as 'non-mandatory and I know that is not true.
I need the DAX to basically say if 

1. in scope is 'Yes'

and

2. Priority is 'High

and

3. Case type is 'In-house'

and

4. ANY of the vechile types related to the case is 'Hatchback' then the result is "mandatory" otherwise "discretionary"

[v-yueyunzh-msft]

Hi, @JamesBockett 

Thanks for your quick response!

I check it in my test data , i think the logic has no problem,you can see each parameter return per row:

In your IF() logic , it actualy return the "Discretionary".

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly