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
PowerUser39
Frequent Visitor

DAX to Power Query - IF Statement

Hello,

 

I am trying to write the below DAX in Power Query's Custom Column but getting some errors and not getting intended results same as DAX.

 

DAX Code to calculate calendar years interval.

IF(
    AND(
        ISNUMBER([TEST1_YEAR]),
        IF(ISNUMBER([TEST2_YEAR]), YEAR(TODAY()) - [TEST1_YEAR], YEAR(TODAY()) - [TEST1_YEAR]) <= 6
    ),
    "Yes",
    IF(
        OR(
            AND(
                ISNUMBER([TEST3_YEAR]),
                [TEST3_YEAR] <= YEAR(TODAY())
                && [TEST3_YEAR] >= YEAR(TODAY()) - 6
            ),
            ISBLANK([TEST4])
        ),
        "Yes",
        "No"
    )
 
My Power Query Custom Column I have so far:
 

if Value.Is([TEST1_YEAR],type number) and Value.Is([TEST2],type number) and Date.Year(DateTime.LocalNow()) - [TEST1_YEAR] <= 6 then "Yes" else

if Value.Is([TEST3_YEAR],type number) and ([TEST3_YEAR] <= Date.Year(DateTime.LocalNow()) and [TEST3_YEAR] >= Date.Year(DateTime.LocalNow()) - 6) or [TEST4] = "" then "Yes" else "No"

 

Not sure what I am doing wrong here. Can someone please help me with correct query?

1 ACCEPTED SOLUTION

@PowerUser39 OK, I don't get any errors and just one difference, is this what you are seeing as well? One thing is that I don't quite understand your DAX, you seem to have a useless IF statement in there as it returns the same thing regardless (line 4). Also, be careful with using "" as you might want to change this to null in PQ. PBIX is attached.

 


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...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@PowerUser39 I'm guessing that your errors are coming from comparing datetime to number or something else related to datetime. Can you provide sample data so I can put this into a PBIX file and test?


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...

Sure it is a huge dataset but it looks similar to below

 

TEST1TEST2TEST3TEST4
2020 2020 
2020 20205
2023201820135
202220182005 
2022 20055
2020201720096

@PowerUser39 OK, I don't get any errors and just one difference, is this what you are seeing as well? One thing is that I don't quite understand your DAX, you seem to have a useless IF statement in there as it returns the same thing regardless (line 4). Also, be careful with using "" as you might want to change this to null in PQ. PBIX is attached.

 


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...

Thanks Greg. I inserted another condition to exclude the Null value and it worked for me.

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors