If Statement Working With Two Seperate Database Tables

[Maverick_BI]

Hello Everyone,

I have an request that has been causing me some trouble on how to resolve.

I have a need for a new column to be created which uses an If or Case statement where possible.

I need to link two tables together and then based on the result show it in a new column the value either Order or No Order.

In this example the Customer Table is a 1 to a Many on the Order Table with a cross filter direction of Single.

### SQL Statement ###

SELECT
<<< REMOVED FIELDS NOT NEEDED >>>
FROM SYSADM.CUSTOMER
WHERE NOT EXISTS (SELECT AH_IDENT FROM SYSADM.ORDER WHERE SYSADM.CUSTOMER.ID = SYSADM.ORDER.AH_IDENT
AND SYSADM.CUSTOMER.ID != '0'
AND NOT SYSADM.CUSTOMER.LOCKED = '1' OR SYSADM.CUSTOMER.LOCKED = '3'

### Attempted IF Statement ###

$Column Customer Ordered = IF(
'SYSADM ORDER'[AH_IDENT] = 'SYSADM CUSTOMER'[ID],
'SYSADM CUSTOMER'[ID] <> "0",
NOT 'SYSADM CUSTOMER'[LOCKED] IN "1","3"
,"Order","No Order"
)

When I use the following statement it advises that - Too many arguments were pased to the IF function. The maximum argument count for the function is 3.

Cheers, Everyone.

[rajulshah]

@Maverick_BI 

You can use the following DAX function:

$Column Customer Ordered = IF(
'SYSADM ORDER'[AH_IDENT] = 'SYSADM CUSTOMER'[ID] && 
'SYSADM CUSTOMER'[ID] <> "0" ||
NOT 'SYSADM CUSTOMER'[LOCKED] IN "1","3"
,"Order","No Order"
)

Let me know if this didn't help.

[Maverick_BI]

Hello rajulshah,

Thank you for the quick response unfortunately the same message appeared as what I am trying to resolve as shown below.

[rajulshah]

@Maverick_BI Are you creating a new measure or new column?

[Maverick_BI]

@rajulshahI was originally attempting to do this as a new column, however if I need to do it as a measure then I have no concerns in doing it this way.

[rajulshah]

@Maverick_BI , If you are creating a calculated measure, then you need to use an aggregated value for columns, for instance, you can use SELECTEDVALUE.

$Column Customer Ordered = IF(
SELECTEDVALUE('SYSADM ORDER'[AH_IDENT]) = 'SYSADM CUSTOMER'[ID] && 
SELECTEDVALUE('SYSADM CUSTOMER'[ID]) <> "0" ||
NOT SELECTEDVALUE('SYSADM CUSTOMER'[LOCKED]) IN {"1","3"}
,"Order","No Order"
)

Please let me know if this didn't work.

[Maverick_BI]

Hello @rajulshah, the formula is accepted in PowerBI - thank you for that answer, however I am now getting the following message.

A single value for column ‘ID’ in table ‘SYSADM CUSTOMER’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

In our database we have a connection where the tables - Customer (one) can have Orders (many) - anyway to get around this or are we out of luck? Cheers.

[rajulshah]

@Maverick_BI 
I would need to look at the data to see why is it giving error because we have already used aggregation in the measure.