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 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"
)
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?
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/DenSelimovicNow 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" )