DAX query with text field

[Anonymous]

Hello,

Can anyone help by correcting the DAX query below to pull a text field from one table to another?

The Staff Table demonstrates all staff members (identified by Username), and which team they belonged to (ServiceDisplayName) at a certain date (In Month). The Contact Table demonstrates which staff recorded a contact (indentified by ConsultationUsername) at a certain date (ConsultationMonth). Because staff have moved teams and performed multiple contacts the tables have been joined Many:Many on Username and ServiceDisplayName. Both of these fields are text based.

My problem is I can't find a suitable command to replace DISTINCTCOUNT below. It may be I need to write the formula in a totally different way, but hopefully below demonstrates what I'm trying to do. 

ESR-UsernameService = IF('Contact Table'[ConsultationMonth].[Date]=DATE(2019,04,01), CALCULATE(DISTINCTCOUNT('Staff Table'[ServiceDisplayName]), 'Staff Table'[In Month]=DATE(2019, 04, 01)),
IF('Contact Table'[ConsultationMonth].[Date]=DATE(2019,05,01), CALCULATE(DISTINCTCOUNT('Staff Table'[ServiceDisplayName]), 'Staff Table'[In Month]=DATE(2019, 05, 01)),
IF('Contact Table'[ConsultationMonth].[Date]=DATE(2019,06,01), CALCULATE(DISTINCTCOUNT('Staff Table'[ServiceDisplayName]), 'Staff Table'[In Month]=DATE(2019, 06, 01)),
IF('Contact Table'[ConsultationMonth].[Date]=DATE(2019,07,01), CALCULATE(DISTINCTCOUNT('Staff Table'[ServiceDisplayName]), 'Staff Table'[In Month]=DATE(2019, 07, 01)),
IF('Contact Table'[ConsultationMonth].[Date]=DATE(2019,08,01), CALCULATE(DISTINCTCOUNT('Staff Table'[ServiceDisplayName]), 'Staff Table'[In Month]=DATE(2019, 08, 01)),
IF('Contact Table'[ConsultationMonth].[Date]=DATE(2019,09,01), CALCULATE(DISTINCTCOUNT('Staff Table'[ServiceDisplayName]), 'Staff Table'[In Month]=DATE(2019, 09, 01)),
IF('Contact Table'[ConsultationMonth].[Date]=DATE(2019,10,01), CALCULATE(DISTINCTCOUNT('Staff Table'[ServiceDisplayName]), 'Staff Table'[In Month]=DATE(2019, 10, 01)),
IF('Contact Table'[ConsultationMonth].[Date]=DATE(2019,11,01), CALCULATE(DISTINCTCOUNT('Staff Table'[ServiceDisplayName]), 'Staff Table'[In Month]=DATE(2019, 11, 01)),
IF('Contact Table'[ConsultationMonth].[Date]=DATE(2019,12,01), CALCULATE(DISTINCTCOUNT('Staff Table'[ServiceDisplayName]), 'Staff Table'[In Month]=DATE(2019, 12, 01)),
BLANK())))))))))

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

If I understand you correctly, please try Merge Queries feature in Query Editor.

For more details please check the document below.

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data#combine-queries.

Here's my sample data and result would be show as below.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.