Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all,
I'm stuck with this formula from few days. Tried the MS Copilot but still getting the below 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.
I've 2 tables:
1) FiT4 Initiative All where I've a column with text value named Phase__c.
2) Initiative benefit, where I've a column with a Latest Estimate (LE) value (decimal number).
I would like to have a formula that shows, in example, that if the Phase__c is "L0" then the Latest Estimate is equal to zero. This is for L0-L2. Then L3 the latest Estimate is *0.5, L4 is *0.9 and L5 and L6 equals Latest Estimate.
I've used several approaches to it, however for all of them I got the same error. Below are the formulas I've used.
Kindly appreciate if someone could provide a solution for this.
Many thanks in advance,
PoS LE =
IF ( ISBLANK ( [Year__c] ) || [Year__c] = "", 0, SWITCH ( TRUE (), RELATED('FiT4 Initiative ALL'[Phase__c]) = "L0", [Latest Estimate (LE)] * 0, RELATED('FiT4 Initiative ALL'[Phase__c]) = "L1", [Latest Estimate (LE)] * 0, RELATED('FiT4 Initiative ALL'[Phase__c]) = "L2", [Latest Estimate (LE)] * 0, RELATED('FiT4 Initiative ALL'[Phase__c]) = "L3", [Latest Estimate (LE)] * 0.5, RELATED('FiT4 Initiative ALL'[Phase__c]) = "L4", [Latest Estimate (LE)] * 0.9, RELATED('FiT4 Initiative ALL'[Phase__c]) = "L5", [Latest Estimate (LE)], RELATED('FiT4 Initiative ALL'[Phase__c]) = "L6", [Latest Estimate (LE)], 0 ) )
I've also created a new measure extracting the "L" and changing to value the Phase__c (renamed to 6Phase__c):
PoS LE =
IF (
ISBLANK ( [Year__c] ) || [Year__c] = "",
0,
SWITCH (
TRUE (),
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 0, [Latest Estimate (LE)] * 0,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 1, [Latest Estimate (LE)] * 0,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 2, [Latest Estimate (LE)] * 0,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 3, [Latest Estimate (LE)] * 0.5,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 4, [Latest Estimate (LE)] * 0.9,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 5, [Latest Estimate (LE)],
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 6, [Latest Estimate (LE)],
0
)
)
And based on IF formula:
PoS LE =
IF (ISBLANK ( [Year__c] ) || [Year__c] = "",
0,
IF (
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 0, [Latest Estimate (LE)] * 0,
IF (
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 1, [Latest Estimate (LE)] * 0,
IF (
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 2, [Latest Estimate (LE)] * 0,
IF (
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 3, [Latest Estimate (LE)] * 0.5,
IF (
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 4, [Latest Estimate (LE)] * 0.9,
IF (
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 5, [Latest Estimate (LE)],
IF (
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 6, [Latest Estimate (LE)],
0
)
)
)
)
)
)
)
)
And finally trying to use the "value sintax" which the error was referring to:
PoS LE = IF ( ISBLANK ( [Year__c] ) || [Year__c] = "", 0, SWITCH ( TRUE (), VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 0, [Latest Estimate (LE)] * 0, VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 1, [Latest Estimate (LE)] * 0, VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 2, [Latest Estimate (LE)] * 0, VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 3, [Latest Estimate (LE)] * 0.5, VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 4, [Latest Estimate (LE)] * 0.9, VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 5, [Latest Estimate (LE)], VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 6, [Latest Estimate (LE)], 0 ) )
Solved! Go to Solution.
Hi all,
I just have found the error and is working fine now. Thanks for spending the time to view it. The correct formula is:
PoS LE =
SWITCH (
TRUE (),
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 0, [Latest Estimate (LE)] * 0,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 1, [Latest Estimate (LE)] * 0,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 2, [Latest Estimate (LE)] * 0,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 3, [Latest Estimate (LE)] * 0.5,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 4, [Latest Estimate (LE)] * 0.9,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 5, [Latest Estimate (LE)],
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 6, [Latest Estimate (LE)],
0
)
)
Hi all,
I just have found the error and is working fine now. Thanks for spending the time to view it. The correct formula is:
PoS LE =
SWITCH (
TRUE (),
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 0, [Latest Estimate (LE)] * 0,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 1, [Latest Estimate (LE)] * 0,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 2, [Latest Estimate (LE)] * 0,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 3, [Latest Estimate (LE)] * 0.5,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 4, [Latest Estimate (LE)] * 0.9,
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 5, [Latest Estimate (LE)],
RELATED('FiT4 Initiative ALL'[6Phase__c]) = 6, [Latest Estimate (LE)],
0
)
)
@NelioKucharski , Try using
DAX
PoS LE =
IF (
ISBLANK ( [Year__c] ) || [Year__c] = "",
0,
SWITCH (
TRUE (),
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 0, [Latest Estimate (LE)] * 0,
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 1, [Latest Estimate (LE)] * 0,
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 2, [Latest Estimate (LE)] * 0,
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 3, [Latest Estimate (LE)] * 0.5,
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 4, [Latest Estimate (LE)] * 0.9,
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 5, [Latest Estimate (LE)],
VALUE(RELATED('FiT4 Initiative ALL'[Phase__c])) = 6, [Latest Estimate (LE)],
0
)
)
Proud to be a Super User! |
|
Hi, this is the last formula I've posted in my message... also doesn't work
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
10 | |
6 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |