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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
g60dave
Frequent Visitor

IF statement while ignoring blanks/null help

Hi All

I cant seem to crack this one. I have 5 columns and I'm trying to create an if statement which will result in either true of false.

 

i would like the statement to look at all the columns A to E and if the data matches and also ignores blanks/null data then the result is true

If any of the columns do not match while ignoring the blanks/null then the results should be false. 

 

2021-07-30 21_00_55-All Columns that need a QG direct link - Excel.png

 

thank you in advance to anyone that can offer assistance

 

 

4 REPLIES 4
g60dave
Frequent Visitor

thanks for the suggestions, an unpivoted table doesn't work in this case. This is a database connected to D365

 

But I like the idea of a distinctcountno blanks. I just wish I knew how to make that work across one row? any other suggestions?

wdx223_Daniel_0-1627868749805.png

M might be better

wdx223_Daniel_1-1627868901592.png

 

Thanks Daniel, ive added a customn column

 

But i don't think this works, as the columns I want to work with are from merge queries 

 

Can you offer any more advice?

 

To give you more information, the other table is user related and includes the country of the user. The table I included merge queries is to do with the projects, and shows which countries are fulfilling that position in each project

AlexisOlson
Super User
Super User

If columns A through E were unpivoted, this would be as simple as checking if DISTINCTCOUNTNONBLANK were greater than 1 or not.

 

You might be able to do something similar along the lines of this

COUNTROWS ( UNION ( { [A], [B] }, { [C], [D], [E] } ) ) <= 1

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors