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

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
Super User

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

8 REPLIES 8
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

Frequent Visitor

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:)

Thanks for help!

Frequent Visitor

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

Super User

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

Frequent Visitor

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!

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

Super User

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

Frequent Visitor

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.

Frequent Visitor

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

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

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors