Forum Discussion

RMDNA's avatar
RMDNA
Solution Sage
9 years ago

Simple issue: whole number comparison

All,

 

I'm trying to compare years between two tables, so I've separated the year out from the date type and set it to whole number datatype. I'm trying to create a simple column of "if this year doesn't equal the other year, then "Text" else "Text2".

 

In PowerQuery custom column, I've done:

 

if(
[Fiscal Year 1 Number] <> "Table 2"[Fiscal Year 2 Number]
)
then "Yes" else "No"

 

I get:

Expression.Error: We cannot apply field access to the type Text.
Details:
    Value=Table 2
    Key=Fiscal Year 2

 

Both fiscal year values are set to whole number. Any help?

Need Help

2 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    I guess this code is in query "Table 1"?

     

    If you want to refer to "Table 2" you need a # (#"Table 2"), but then again you are refering to a complete column in Table 2, or - in other words - to a list of values.

     

    Somehow you need to get individual values, typically by merging the 2 tables so you have values from Table 1 on the same row as the values from Table 2. Then you can simply use:

    if(
    [Fiscal Year 1 Number] <> [Fiscal Year 2 Number]
    )
    then "Yes" else "No"

     

     

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi RMDNA,

     

    Is there any relationship between your Table 1 and Table 2? If there is a "Many to one" (*:1) relationship between Table 1 and Table 2, then you should be able to simply use the following DAX formula to add a calculate column in Table 1 under Modeling tab. :smileyhappy:

     

    New Column =
IF (
    'Table 1'[Fiscal Year 1 Number] <> RELATED ( 'Table 2'[Fiscal Year 2 Number] ),
    "Yes",
    "No"
)

     

    Regards