topic Re: Help removing specific values tied to a shared ID value in DAX Commands and Tips

Help removing specific values tied to a shared ID value

sc27 — Fri, 11 Jun 2021 18:41:21 GMT

Hello,

I have a large dataset where a number of records are connected via unique IDs. I want to be able to see the associated attributes across all ID's and remove specific values.

To simplify it, I want to check for where an ID has more than 1 Account related to it. If it does, and that Account name is John, I do not want to include it within a separate concatenate measure.

I'd want my CONCATENATEX for Account, State and Level to give me this result

Is this even possible?

Re: Help removing specific values tied to a shared ID value

Anonymous — Fri, 11 Jun 2021 22:03:09 GMT

sc27 Something like this - https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Studio-Group-by-Concatenate-Textcombine-listagg-Script/m-p/783327

Re: Help removing specific values tied to a shared ID value

sc27 — Fri, 11 Jun 2021 23:26:36 GMT

Thanks, but that's not exactly what I need. I have a scenario where I need the remove a specifc value and its associated column values based on a logical condition. Then, take that result and conduct a separate concatenation

Re: Help removing specific values tied to a shared ID value

Jihwan_Kim — Fri, 11 Jun 2021 23:46:06 GMT

Hi, sc27

Please check the link down below.

Account Name(s) Measure =

IF (

ISFILTERED ( 'Table'[ID] ),

CONCATENATEX (

SUMMARIZE ( FILTER ( 'Table', 'Table'[Account] <> "John" ), 'Table'[Account] ),

'Table'[Account],

";"

)

State(s) Measure =

IF (

ISFILTERED ( 'Table'[ID] ),

CONCATENATEX (

SUMMARIZE ( FILTER ( 'Table', 'Table'[Account] <> "John" ), 'Table'[State] ),

'Table'[State],

";"

)

Level(s) Measure =

IF (

ISFILTERED ( 'Table'[ID] ),

CONCATENATEX (

SUMMARIZE ( FILTER ( 'Table', 'Table'[Account] <> "John" ), 'Table'[Level] ),

'Table'[Level],

";"

)

https://www.dropbox.com/s/6zhmvdihptxofkd/sc27.pbix?dl=0

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

Re: Help removing specific values tied to a shared ID value

sc27 — Sat, 12 Jun 2021 00:13:53 GMT

I didn't think this would matter, but in my data, the ID is in one table (its a Fact table) and the Account, State and Level are all within one other. I tried to do what you sent over but it just concatenated every single Account, paying no mind to the relationship between the ID and the correct Accounts

Re: Help removing specific values tied to a shared ID value

Jihwan_Kim — Sat, 12 Jun 2021 02:29:07 GMT

Hi, sc27

Please share your sample pbix file's link here.

Re: Help removing specific values tied to a shared ID value

sc27 — Mon, 14 Jun 2021 14:59:45 GMT

Unfortunately, you won't be able to see it. Access is maintained by an AD group and the data is sourced from a database which is also internal