DAX: How to concatenate strings in CALCULATE and persist the filters

pacifist — Thu, 16 Nov 2023 06:24:46 GMT

Hi,

I know I can concatenate values in PowerQuery, but out of curiosity, I wanted to do in the DAX on the runtime. Seems like I'm unable to do it effectively along with constantly losing the context.

table: DSN_USERS_VW

DSN_ID	USER_GROUP	IDENTIFIER	PRINCIPAL_TYPE
1	APP-PBI-DSN-GROUP_A	id-of-the group	group
1	marcello.piatti@...com		user
1	anna.marina@...com		user

I'm trying to build a link to the portal.azure.com to which see the members of the group if the PRINCIPAL_TYPE is group, if it's user, return blank.

_render_asLink = CALCULATE( IF( MAX(DSN_USERS_VW[PRINCIPAL_TYPE]) = "Group", CONCATENATE("htt..portal.azure.com/../groupId/", MAX(DSN_USERS_VW[IDENTIFIER])), BLANK() ), KEEPFILTERS(DSN_USERS_VW) )

I seriously don't like having to use the aggregation but seem like I have it. Is this the best way to do it?

When I say it ignores the context, I have another table DSN_VW and the table visual using the data. normally, if I don't have the _render_asLink measunre in the other visual, the DSN_VW filters DSN_USERS_VW nicely, however, with the measure in the latter it always returns all of them. I think I might be having the context wrong there...

Thanks for all your help!

Re: DAX: How to concatenate strings in CALCULATE and persist the filters

Rupak_bi — Thu, 16 Nov 2023 10:22:07 GMT

try this:

_render_asLink = CALCULATE( IF( MAX(DSN_USERS_VW[PRINCIPAL_TYPE]) = "Group", "htt..portal.azure.com/../groupId/"& MAX(DSN_USERS_VW[IDENTIFIER]), BLANK() ), KEEPFILTERS(DSN_USERS_VW) )

topic Re: DAX: How to concatenate strings in CALCULATE and persist the filters in DAX Commands and Tips

DAX: How to concatenate strings in CALCULATE and persist the filters

Re: DAX: How to concatenate strings in CALCULATE and persist the filters