Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
afmcjarre
Helper I
Helper I

DAX Formula for Conditional Calculation

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:

DAX.png

I'm very new to DAX and typically use Power Query or calculated columns to get the answers I need.  

3 REPLIES 3
ZikoPowerBI
Helper II
Helper II

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:

 

2021-05-07_16h39_02.png

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.