Forum Discussion
Using countif based on 4 columns
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
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/
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!
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovic
