Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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
Solved! Go to Solution.
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”.
Proud to be a Super User! | |
Thanks for the explaination. The calculated column was exactly what I needed.
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”.
Proud to be a Super User! | |
User | Count |
---|---|
12 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
19 | |
14 | |
8 | |
7 |