Forum Discussion
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 place to say IF Customer has Cloud AND On Prem then output migrated. The table below is an example of what i am trying to accomplish;
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,
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
4 Replies
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())
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?
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.
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