Forum Discussion
DAX query with text field
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())))))))))
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.
2 Replies
- amitchandak
Super User
To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.
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.
