cancel
Showing results 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

Helper III

## If sum and countif multiple columns ranges in DAX

Hi,
I have 6 columns are in my data sheet A,B&C , AA,BB&CC, the data type are mixed (number and text) based on the 6 columns I am trying to make a if statement.
I am looking for measure and new calculate column option, I can't use the Power query becuase those 6 columns came from different tabel.

 IF DATA OR VALUE ONLY COLUMNS FROM AA:CC THEN "XX2". IF THERE IS NO VALUE OR BLANKS IN-BETWEEN COLUMNS RANGE A:CC THEN "XXX". IF COLUMNS A-AA, B-BB & C-CC ARE NOT MATCHED THEN "NO". IF COLUMNS A-AA, B-BB & C-CC ARE MATCHED THEN "OK". IF ANYONE OF THEM ARE NOT MATCHED THEN RETURN "NO". IF DATA OR VALUE ONLY COLUMNS FROM A:C THEN "XX1". IF DATA OR VALUES ARE MISSING ANY ONE OF THE COLUMNS (A:C OR AA:CC) THEN RETURN "XX3".

 A B C AA BB CC DESIRED RESULT 2323 1240 1020 XX2 XXX 800 600 350 785 600 600 NO 1700 1200 850 1700 1200 800 NO 800 600 450 XX1 800 600 450 800 600 450 OK 1200 2000 1200 2000 XX3 2000 800 2000 800 XX3 800 800 XX3 1200 2000 1200 2000 200 XX3 2000 800 100 2000 800 XX3 800 25 50 800 XX3 1200 2000 25 1200 2000 XX3 75 2000 800 2000 800 XX3 10 15 800 800 XX3

1 ACCEPTED SOLUTION
Super User

Do you already have them in a single table?

try the following code:

``````Column =
SWITCH (
TRUE (),
[A] = BLANK ()
&& [B] = BLANK ()
&& [C] = BLANK ()
&& (
[AA] <> BLANK ()
|| [BB] <> BLANK ()
|| [CC] <> BLANK ()
), "XX2",
[AA] = BLANK ()
&& [BB] = BLANK ()
&& [CC] = BLANK ()
&& (
[A] <> BLANK ()
|| [B] <> BLANK ()
|| [C] <> BLANK ()
), "XX1",
[A] = BLANK ()
&& [B] = BLANK ()
&& [C] = BLANK ()
&& [AA] = BLANK ()
&& [BB] = BLANK ()
&& [CC] = BLANK (), "XXX",
[A] = BLANK ()
|| [B] = BLANK ()
|| [C] = BLANK ()
|| [AA] = BLANK ()
|| [BB] = BLANK ()
|| [CC] = BLANK (), "XX3",
[A] <> [AA]
|| [B] <> [BB]
|| [C] <> [CC], "NO",
[A] = [AA]
&& [B] = [BB]
&& [C] = [CC], "OK"
)``````

Be very careful of the order of the parameters

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

5 REPLIES 5
Super User

Hi @Saxon202202 ,

How is the data model?

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper III

@MFelix ,

https://www.dropbox.com/s/4tbjc5jruvugg8j/IF%20statement%20multiple%20column-27-12-2022.pbix?dl=0

Here is the PBI file for your reference. The 6 columns are came from different tabels by using DAX.

Super User

Do you already have them in a single table?

try the following code:

``````Column =
SWITCH (
TRUE (),
[A] = BLANK ()
&& [B] = BLANK ()
&& [C] = BLANK ()
&& (
[AA] <> BLANK ()
|| [BB] <> BLANK ()
|| [CC] <> BLANK ()
), "XX2",
[AA] = BLANK ()
&& [BB] = BLANK ()
&& [CC] = BLANK ()
&& (
[A] <> BLANK ()
|| [B] <> BLANK ()
|| [C] <> BLANK ()
), "XX1",
[A] = BLANK ()
&& [B] = BLANK ()
&& [C] = BLANK ()
&& [AA] = BLANK ()
&& [BB] = BLANK ()
&& [CC] = BLANK (), "XXX",
[A] = BLANK ()
|| [B] = BLANK ()
|| [C] = BLANK ()
|| [AA] = BLANK ()
|| [BB] = BLANK ()
|| [CC] = BLANK (), "XX3",
[A] <> [AA]
|| [B] <> [BB]
|| [C] <> [CC], "NO",
[A] = [AA]
&& [B] = [BB]
&& [C] = [CC], "OK"
)``````

Be very careful of the order of the parameters

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper III

Yes, all of them in single table.

Your DAX code is working fine but the order of parameters, && and or conditons is very tricky to apply in switch function. Is there any other alternative way to achieve it? Please advice.

Super User

Hi @Saxon202202 ,

Since you have different alternatives and you want to have it in dax, this is the best option you have since no matter what you use you always need to do some sort of IF/SWITCH statement.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

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

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors