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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dsandip
Frequent Visitor

IF else is not working

Hi,

I have a sql query condition like below:

CASE
WHEN Right(OPS_CNDCT_REF_PROFIT_CENTRE_NM,8)='Casualty' And Left(INSURED_NM,10)='Knighthood' THEN 'Y'
WHEN (Left(POLICY_NO,4)='0067' Or Left(POLICY_NO,4)='0065') and OPS_CNDCT_REF_PROFIT_CENTRE_NM like '%OCEAN MARINE%' THEN 'Y' ELSE 'N' END
AS "AIG_controlled_Indicator"

 

I am converting them to this below statement:

Column 3 =
IF(
    Right(TDIM_OPS_CNDCT_REF_PROFIT_CENTRE_PBI[OPS_CNDCT_REF_PROFIT_CENTRE_NM],8)="Casualty" && Left(TDIM_POLICY_PBI[INSURED_NM],10)="Knighthood", "Y", if(
            (Left(TDIM_POLICY_PBI[POLICY_NO],4)="0067" || Left(TDIM_POLICY_PBI[POLICY_NO],4)="0065") &&
            CONTAINSSTRING(TDIM_OPS_CNDCT_REF_PROFIT_CENTRE_PBI[OPS_CNDCT_REF_PROFIT_CENTRE_NM], "OCEAN MARINE")
            == TRUE() , "Y")            

)
 
But it gives me the below error:
A single value for column 'POLICY_NO' in table 'TDIM_POLICY_PBI' 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.
 
what is wrong here?
 
Thanks,
Sandip
2 REPLIES 2
Greg_Deckler
Super User
Super User

@dsandip In measures, you need to wrap column references with an aggregator like MAX, MIN, SUM, AVERAGE, etc.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi,

I am not using Measure, rather I am using Calculated column here.

So, what I have to do now.

 

Thanks,

Sandip

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

August Carousel

Fabric Community Update - August 2024

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