Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Lookup value from one table from another

I have a large table with one of the columns as 'CID' and there are multiple rows in this table with the same CID value. There is another table with distinct CID codes that I need to match the previ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous, sounds to me like you have selected "Add Measure" instead of "Add Column"?

    Just check that you have used "Add Column" and done so in the table that will hold the CID Flag.  In this case we are adding it to the CID Table.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Thanks a lot for the solution. And, it has definitely solved part of my issue.

 

I forgot to add another problem to it.

 

So, the counts calculated by the 'CID Flag' is also counting the duplicates since there are rows with same CID values in the larger table. How should I make sure that the Flag only counts the distinct rows from the larger table?

 

Thanks in advance.

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
9 years ago

Anonymous,

 

I asume the large table have the sample data

 

Now you need to count the distinct value for the CIDs that have CID Flag=1, right?

If that is the case, please try to create a measure using the DAX below.
Measure = CALCULATE(DISTINCTCOUNT(Table3[CID]),FILTER(Table3,Table3[CID flag]=1))

 

Regards,

Charlie Liao

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Thanks Charlie. I could get the distinct counts using your logic.