Forum Discussion
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!
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
8 Replies
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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicHey 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?
