Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am currently trying to put the following equation into a DAX measure:
If Expiration Date – Inception Date > 5, (Written Premium * 5) or If Expiration Date – Inception Date < 5, (Written Premium * (Expiration Date – Inception Date)) – Sum of Total Losses and LAE Incurred Column.
It uses data from 3 different tables and I would prefer to use a measure if possible rather than merging the data together and making a caluculated column. I already created a calculated column that gives the (Expiration Date-Inception Date).
These are the two things I have tried:
Measure = IF(working_file_revisions_max_properties[Expiration-Inception]>5,working_file_policy_range[policyWrittenPremium]*5, working_file_policy_range[policyWrittenPremium]*working_file_revisions_max_properties[Expiration-Inception]-working_file_total_claims_and_sum[Sum of Total Losses and LAE Incurred])
Measure = IF(SUMX(working_file_revisions_max_properties,working_file_revisions_max_properties[Expiration-Inception])>5,SUMX(working_file_policy_range,working_file_policy_range[policyWrittenPremium])*5, SUMX(working_file_policy_range,working_file_policy_range[policyWrittenPremium])*SUMX(working_file_revisions_max_properties,working_file_revisions_max_properties[Expiration-Inception])-SUMX(working_file_total_claims_and_sum,working_file_total_claims_and_sum[Sum of Total Losses and LAE Incurred]))
I tried putting SUMX around everything in the equation because of the error message I got with the first version, but that one would never load in my table...just kept spinning:
I'm very new to DAX and typically use Power Query or calculated columns to get the answers I need.
Does this value return a single value- working_file_revisions_max_properties[Expiration-Inception]>5 ?
Put Sum(working_file_policy_range[policyWrittenPremium])
put an aggregation in all the below expressions too:
working_file_policy_range[policyWrittenPremium]*working_file_revisions_max_properties[Expiration-Inception]-working_file_total_claims_and_sum[Sum of Total Losses and LAE Incurred]
The best thing would be put expression in a seperate measure and use those measures in the final expression
Measure 1 =sum(working_file_policy_range[policyWrittenPremium])*5
Measure 2 = sum(working_file_policy_range[policyWrittenPremium])*sum(working_file_revisions_max_properties[Expiration-Inception])-sum(working_file_total_claims_and_sum[Sum of Total Losses and LAE Incurred])
final expresion :IF(working_file_revisions_max_properties[Expiration-Inception]>5,Measure 1,Measure 2)
Yes, a single value is returned for working_file_revisions_max_properties[Expiration-Inception]>5.
What confuses me is why you used sum vs sumX. I don't want to sum all the values in the column...I need to sum by policy which you can't tell in the equation above. I want the summing to happen row by row for my final report.
A single value per ROW is returned. Getting this error on the final expression:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |