Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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"
)
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"
)
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" )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |