How to use DAX to link tables

[sarahmclaughlin]

Hi,

I'm a PowerBI novice, and I am trying to correct a database design error using DAX.

I have two client types (Survivor, Supporter), but only Survivor is linked to a table with the date the service was rendered (service_id). I need to count the number of Supporters per month, but PowerBI cannot see them because they are not linked to a date (through service_id). 
I thought to create a custom column that can find the ClientPI_id that they both share, and then use the same (service_id) found for Survivor to populate the matching blank cell for Supporter. How could I achieve this with DAX? (Pic below)

I really appreciate some help!

Thanks, Sarah

[DimaMD · Accepted solution]

Hi, @sarahmclaughlin  try it

[sarahmclaughlin]

Thank you so much! This worked perfectly!!! I've been stuck on this for weeks! 

[sarahmclaughlin]

Hi! I get a message saying it doesn't recognise MAXX, and documentation says "This function is not supported for use in DirectQuery mode" - https://docs.microsoft.com/en-us/dax/maxx-function-dax 
Is there another function I could try? How did it work on your side? Thanks so much!!!

[DimaMD]

@sarahmclaughlin  You must create a computed column, not a measure, Please provide a screenshot of the error
See example
Example_pbix

[sarahmclaughlin]

[DimaMD · Accepted solution]

@sarahmclaughlin You do not need to do this in PowerQuery, but add a calculated column in PowerBi. 

[DimaMD · Accepted solution]

Hi, @sarahmclaughlin  try it