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

Using countif based on 4 columns

Hi, 
I'd like to perform following excel formula using dax/pq (basically in powerBI).

=IF(COUNTIF(E145:L145,">90")>0,">90D",IF(COUNTIF(E145:L145,">60")>0,">60D<90D",IF(COUNTIF(E145:L145,">30")>0,">30D<60D",IF(COUNTIF(E145:L145,"<14")>0,"<14D",IF(COUNTIF(E145:L145,">14")>0,">14D<30D",0)))))

Tried some variations without success.
Any ideas?

Thanks in advance!

1 ACCEPTED SOLUTION

Hey @hejszyszki ,

 

the MAX functions can just handle 2 values. So happily I would do "MAX( myTable[W1], myTable[W2], myTable[W3], myTable[W4])", but then I receive an error because MAX can just have 2 arguments. For that reason, I split it in 2 MAX functions and get the max of these 2 MAX functions. At least with my knowledge, that's the easiest way in your case.

 

For the blank values you can sure return nothing:

Countif =
VAR vMaxRowValue =
    MAX(
        MAX(
            myTable[W1],
            myTable[W2]
        ),
        MAX(
            myTable[W3],
            myTable[W4]
        )
    )
RETURN
    SWITCH(
        TRUE(),
        vMaxRowValue = BLANK() || vMaxRowValue = "", BLANK(),
        vMaxRowValue > 90, ">90D",
        vMaxRowValue > 60, ">60D<90D",
        vMaxRowValue > 30, ">30D<60D",
        vMaxRowValue > 14, ">14D<30D",
        vMaxRowValue < 14, "<14D"
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

8 REPLIES 8
selimovd
Super User
Super User

Hey @hejszyszki ,

 

yes, that's also possible in Power BI.

Add a new calculated column and use the following formula:

 

Countif =
SWITCH(
    TRUE(),
    myTable[myColumn] > 90, ">90D",
    myTable[myColumn] > 60, ">60D<90D",
    myTable[myColumn] > 30, ">30D<60D",
    myTable[myColumn] > 14, ">14D<30D",
    myTable[myColumn] < 14, "<14D"
)

 

 

Instead of many ifs, I would always go for the combination SWITCH and TRUE. Check out the following article that explains a little better:

https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Hey @selimovd,
Thanks for response. It is not exact output, but i will try to work out off this article 😉 
Basically the idea my excel formula is sth about : If one of values in W1:W4 is <14 mark this item as <14,

if not check if one of value is <30 & >14 (if yes mark as >14<30) etc.. So it is definetly close but not exact:)
196905653_153187510126297_5222543416110482423_n.png

 

Thanks for help!

O wait, it is an answear but instead of 1 column i need to pick 4 right? 

Hey @hejszyszki ,

 

yes exactly, you can just expand the part where we check the values.

I would do it like this:

First you get the maximum value from your row and save it as a variable. For that I would use multiple nested MAX functions that return each the maximum of 2 values.

Afterwards you check for this variable (= the max value per row):

Countif =
VAR vMaxRowValue =
    MAX(
        MAX(
            myTable[W1],
            myTable[W2]
        ),
        MAX(
            myTable[W3],
            myTable[W4]
        )
    )
RETURN
    SWITCH(
        TRUE(),
        vMaxRowValue > 90, ">90D",
        vMaxRowValue > 60, ">60D<90D",
        vMaxRowValue > 30, ">30D<60D",
        vMaxRowValue > 14, ">14D<30D",
        vMaxRowValue < 14, "<14D"
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Magic 🙂
I also realised that i have a lot of blank values. Can we somehow ignore this? (blank output preferably in Countif column) For now it is outputting <14 in Countif column.
Thanks a lot!198007517_317714429894752_6490934017008704328_n.png

 

And also whats the idea of splitting MAX's? Why not just Max of Max 1,2,3,4?

Hey @hejszyszki ,

 

the MAX functions can just handle 2 values. So happily I would do "MAX( myTable[W1], myTable[W2], myTable[W3], myTable[W4])", but then I receive an error because MAX can just have 2 arguments. For that reason, I split it in 2 MAX functions and get the max of these 2 MAX functions. At least with my knowledge, that's the easiest way in your case.

 

For the blank values you can sure return nothing:

Countif =
VAR vMaxRowValue =
    MAX(
        MAX(
            myTable[W1],
            myTable[W2]
        ),
        MAX(
            myTable[W3],
            myTable[W4]
        )
    )
RETURN
    SWITCH(
        TRUE(),
        vMaxRowValue = BLANK() || vMaxRowValue = "", BLANK(),
        vMaxRowValue > 90, ">90D",
        vMaxRowValue > 60, ">60D<90D",
        vMaxRowValue > 30, ">30D<60D",
        vMaxRowValue > 14, ">14D<30D",
        vMaxRowValue < 14, "<14D"
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Now it outputs an error, DAX comparison operations do not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

Countif = VAR vMaxRowValue = MAX( MAX( 'inv dos'[W1], 'inv dos'[W2] ), MAX( 'inv dos'[W3], 'inv dos'[W4] ) ) RETURN SWITCH( TRUE(), vMaxRowValue = BLANK() || vMaxRowValue = 0, BLANK(), vMaxRowValue > 90, ">90D", vMaxRowValue > 60, ">60D<90D", vMaxRowValue > 30, ">30D<60D", vMaxRowValue > 14, ">14D<30D", vMaxRowValue <= 14, "<14D" )

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.