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 All,
I have created an excel formula, which is basically comparing 4 columns using IF, AND & SUMIF functions
Excel formula:
=IF(AND(Q2>M2,SUMIFS($Q:$Q,$A:$A,$A2,$J:$J,$J2)>=SUMIFS($M:$M,$A:$A,$A2,$J:$J,$J2)),"Not enough fcst on the VT",IF(AND(Q2>M2,SUMIFS($Q:$Q,$A:$A,$A2,$J:$J,$J2)<=SUMIFS($M:$M,$A:$A,$A2,$J:$J,$J2)),"Enough fcst on the VT but not on the Loc","Enough fcst on the VT & Loc"))
Similar to excel formula, tried to create a new DAX but I am getting bellow error
"Total Demand" is only Measure created others are direct columns from 'Main Table'
TEST =
Hey @harirao
In your DAX has a lot of brackets and parentheses left over. Take a look at the clean DAX below. I did not check the logic of the DAX, I just check the brackets and parentheses. If not works, maybe you should try to use the SWITCH function (SWITCH function (DAX) - DAX | Microsoft Learn). It's easier to write than a cascade IF.
Kind Regards,
Marcel
** If this posts helps, please consider mark it as solved.
TEST =
IF (
AND (
'Main Table'[Total Demand] > 'Main Table'[Final Fcst],
CALCULATE (
SUM ( 'Main Table'[Total Demand] ),
FILTER (
ALL ( 'Main Table'[Product], 'Main Table'[Time[Month] ),
'Main Table'[Product] = EARLIER ( 'Main Table'[Product] )
&& 'Main Table'[Time[Month] = EARLIER ( 'Main Table'[Time[Month] )
)
)
>= CALCULATE (
SUM ( 'Main Table'[Final Fcst] ),
FILTER (
ALL ( 'Main Table'[Product], 'Main Table'[Time[Month] ),
'Main Table'[Product] = EARLIER ( 'Main Table'[Product] )
&& 'Main Table'[Time[Month]] = EARLIER('Main Table'[Time[Month]
)
)
),
"Not enough fcst on the VT",
IF (
AND (
'Main Table'[Total Demand] > 'Main Table'[Final Fcst],
CALCULATE (
SUM ( 'Main Table'[Total Demand] ),
FILTER (
ALL ( 'Main Table'[Product], 'Main Table'[Time[Month] ),
'Main Table'[Product] = EARLIER ( 'Main Table'[Product] )
&& 'Main Table'[Time[Month] = EARLIER ( 'Main Table'[Time[Month] )
)
)
<= CALCULATE (
SUM ( 'Main Table'[Final Fcst] ),
FILTER (
ALL ( 'Main Table'[Product], 'Main Table'[Time[Month] ),
'Main Table'[Product] = EARLIER ( 'Main Table'[Product] )
&& 'Main Table'[Time[Month] = EARLIER ( 'Main Table'[Time[Month] )
)
)
),
"Enough fcst on the VT but not on the Loc",
"Enough fcst on the VT & Loc"
)
)
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |