topic Re: DAX Measure with SWITCH in DAX Commands and Tips

DAX Measure with SWITCH

PSHAW — Fri, 24 May 2024 16:21:41 GMT

I have 2 tables, Agent and Agent Licenses with a 1 to many relationship. On the the Agent Licenses table there is a column called

LicenseExpireDays. I want to create a chart showing expired, will expire in 7 days, etc. The issue is I get a DAX error message when I reference LicenseExpireDays in the swtich command, but it has no issues if I just count on the field. For example this is fine:

Test = Count('Agent Licenses'[LicenseExpireDays])

But this isn't:

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

Exact error is A single value for column 'LicenseExpireDays' in table 'Agent Licenses' 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.

I tried defining LicenseExpireDays as a count but still the same result. What am I missing? Thank you.

Re: DAX Measure with SWITCH

Kaviraj11 — Fri, 24 May 2024 17:14:00 GMT

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”.

Re: DAX Measure with SWITCH

PSHAW — Fri, 24 May 2024 18:56:39 GMT

Thanks for the explaination. The calculated column was exactly what I needed.