DAX Measure with SWITCH

The error you’re encountering is because DAX is expecting a single value for the ‘LicenseExpireDays’ column in the ‘Agent Licenses’ table, but it’s getting multiple values because of the one-to-many relationship between the ‘Agent’ and ‘Agent Licenses’ tables.

In DAX, when you reference a column directly, it expects a single value. If there are multiple values (as in a column of a table), you need to use an aggregation function to get a single result, such as MIN, MAX, COUNT, SUM, etc.

In your case, you’re trying to create a measure that depends on the value of ‘LicenseExpireDays’ for each row in the ‘Agent Licenses’ table. You can achieve this by using the CALCULATE and FILTER functions in DAX. Here’s how you can modify your ‘Test2’ measure:

Test2 = 
CALCULATE(
    COUNT('Agent Licenses'[LicenseExpireDays]),
    FILTER(
        'Agent Licenses',
        'Agent Licenses'[LicenseExpireDays] = 0
    )
)

This measure will count the number of ‘LicenseExpireDays’ that are equal to 0. If you want to return “Expired” when ‘LicenseExpireDays’ is 0 and “OK” otherwise, you can create a calculated column instead of a measure:

LicenseStatus = 
SWITCH(
    TRUE(),
    'Agent Licenses'[LicenseExpireDays] = 0, "Expired",
    "OK"
)

This calculated column will add a new column ‘LicenseStatus’ to the ‘Agent Licenses’ table. For each row in the table, if ‘LicenseExpireDays’ is 0, ‘LicenseStatus’ will be “Expired”. Otherwise, ‘LicenseStatus’ will be “OK”.