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