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
Saxon202202
Helper III
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".

 

ABCAABBCCDESIRED RESULT
   232312401020XX2
      XXX
800600350785600600NO
1700120085017001200800NO
800600450   XX1
800600450800600450OK
12002000 12002000 XX3
 2000800 2000800XX3
  800  800XX3
12002000 12002000200XX3
 20008001002000800XX3
  8002550800XX3
120020002512002000 XX3
752000800 2000800XX3
1015800  800XX3

Saxon202202_0-1672063160248.png

 

 

 

 

 

 

1 ACCEPTED SOLUTION

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
MFelix
Super User
Super User

Hi @Saxon202202 ,

 

How is the data model?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix ,
Thanks for your reply.

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.

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix ,

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.  

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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