Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

DAX Formula Help lookup

Hi, i am trying to work out if a customer ID had migrated from Cloud to on Prem. A customer ID would come up multiple times for cloud or on prem. What i need to do as be able to put something in plac...
Need Help
  • PattemManohar's avatar
    PattemManohar
    8 years ago

    Anonymous Please try this..

     

    Migrated = 
VAR _CloudCheck = LOOKUPVALUE(Test63[CloudorPrem],Test63[CustomerID],Test63[CustomerID],Test63[CloudorPrem],"Cloud")
VAR _OnPremCheck = LOOKUPVALUE(Test63[CloudorPrem],Test63[CustomerID],Test63[CustomerID],Test63[CloudorPrem],"On Prem")
RETURN IF(_CloudCheck<>BLANK() && _OnPremCheck <> BLANK(),TRUE(),FALSE())

  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    Anonymous,

     

    You may also refer to the DAX below.

    Column =
CALCULATE (
    DISTINCTCOUNT ( Table1[Cloud or Prem] ),
    FILTER ( Table1, Table1[Customer ID] = EARLIER ( Table1[Customer ID] ) )
)
    = 2
PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
8 years ago

Anonymous Please try this..

 

Migrated = 
VAR _CloudCheck = LOOKUPVALUE(Test63[CloudorPrem],Test63[CustomerID],Test63[CustomerID],Test63[CloudorPrem],"Cloud")
VAR _OnPremCheck = LOOKUPVALUE(Test63[CloudorPrem],Test63[CustomerID],Test63[CustomerID],Test63[CloudorPrem],"On Prem")
RETURN IF(_CloudCheck<>BLANK() && _OnPremCheck <> BLANK(),TRUE(),FALSE())

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    PattemManohar Thank you very much for the swift reply i am getting the following error with this 

     

    Column 'CustomerID' in table 'CustomerOrderItems' cannot be found or may not be used in this expression.

     

    Is there naything i can do to get past this error?

     

    • PattemManohar's avatar
      PattemManohar
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Anonymous Please make sure the column names are same as it was present in your table. I might have different names for my test data.