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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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!:
The Definitive Guide to Power Query (M)

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.