cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
cgkas Member
Member

How to get unique values excluding one value - DAX formula?

Hello to all,
 

With the following DAX formula I concatenate all unique values of Column3

Column4 = CONCATENATEX(
VALUES(Table1[Column3]),Table1[Column3],", ")

with result

ABC,DEF,WXYZ,HHT

I would like to concatenate all unique values, except the value "WXYZ"

 

I've tried this:

Column4 = CALCULATE(
        CONCATENATEX(
 VALUES(Table1[Column3]),Table1[Column3],", "),
       FILTER ( Table1, 
FIND( "WXYZ", Table1[Column3],, 0 ) = 0 )
)

But I get circular dependency was detected: 

 

My expected output would be:

ABC,DEF,HHT

 

UPDATE

Below from ID to Column3 is my input table and I'd like to get the unique values (excluding "NOT AVAILABLE") in Column4 for each ID(0,1,2,3) and each value in Column2 (A and B). So in Column4 (output column) would look like this:

 

IDColumn1Column2Column3Column4
051234BNOT AVAILABLEKTGGTG
177567AHWUSHWUS,TKYHNY
2123149674AHCGUSGHCGUSG,TKYHNY
214047259AHCGUSGHCGUSG,TKYHNY
371369756ARCNGBNRCNGBN
0502609BKTGGTGKTGGTG
01312314AHCGUSGHCGUSG
1775ATKYHNYHWUS,TKYHNY
2775ATKYHNYHCGUSG,TKYHNY
31989707BUT1TY1HWUS,KTGGTG,UT1TY1
3532715ARCNGBNRCNGBN
3502991BKTGGTGHWUS,KTGGTG,UT1TY1
3120631BHWUSHWUS,KTGGTG,UT1TY1
31320334BNOT AVAILABLEHWUS,KTGGTG,UT1TY1
331234BUT1TY1HWUS,KTGGTG,UT1TY1
347259BKTGGTGHWUS,KTGGTG,UT1TY1

 

Thanks in advance for any help.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: How to get unique values excluding one value - DAX formula?

@cgkas -

Here's the best I could come up with:

Create a table as:

Table = 
SUMMARIZE (
    Table1,
    Table1[ID],
    Table1[Column2],
    "temp", CONCATENATEX (
        FILTER (
            DISTINCT(Table1[Column3]),
            FIND (
                "NOT AVAILABLE",
                Table1[Column3],
                ,
                0
            ) = 0
        ),
        Table1[Column3],
        ", "
    )
)

Column4 as:

Column4 = 
LOOKUPVALUE(
    'Table'[temp],
    'Table'[ID],Table1[ID],
    'Table'[Column2],Table1[Column2]
)

Produces:

5.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
Super User IV
Super User IV

Re: How to get unique values excluding one value - DAX formula?

Can you filter in CONCATENATEX 

 

Column4 = CALCULATE(
        CONCATENATEX(
 FILTER ( Table1, 
FIND( "WXYZ", Table1[Column3],, 0 ) = 0 ),Table1[Column3],", "),
       
)

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information.
Thanks.

 





Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Super User I
Super User I

Re: How to get unique values excluding one value - DAX formula?

@cgkas -

This seems to work:

Column5 =
VAR _filter =
    FILTER (
        Table1,
        FIND (
            "WXYZ",
            Table1[Column3],
            ,
            0
        ) = 0
    )
RETURN
    CONCATENATEX (
        _filter,
        Table1[Column3],
        ", "
    )




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




cgkas Member
Member

Re: How to get unique values excluding one value - DAX formula?

Thanks for your help amitchandak,

 

I'm getting error "Argument 2 in CALCULATE is required"

cgkas Member
Member

Re: How to get unique values excluding one value - DAX formula?

Hello ChrisMendoza, Thanks for your help. I'm receiving as output all values, not only unique ones. I'm testing in DAX Studio in Excel 2016.

Super User I
Super User I

Re: How to get unique values excluding one value - DAX formula?

@cgkas -

I presumed your sample table looked like the below, is that not the case?:

4.png

Please provide an accurate sample.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User I
Super User I

Re: How to get unique values excluding one value - DAX formula?


@amitchandak wrote:

 

Column4 = CALCULATE(
        CONCATENATEX(
 FILTER ( Table1, 
FIND( "WXYZ", Table1[Column3],, 0 ) = 0 ),Table1[Column3],", "),
       
)

 

 There's an extra comma giving you the error message.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




cgkas Member
Member

Re: How to get unique values excluding one value - DAX formula?


@ChrisMendoza wrote:

@cgkas -

I presumed your sample table looked like the below, is that not the case?:

4.png

Please provide an accurate sample.



@ChrisMendoza wrote:

@cgkas -

I presumed your sample table looked like the below, is that not the case?:

4.png

Please provide an accurate sample.


Hi @ChrisMendoza

 

I've updated my original post showing a sample table and output.

 

Thanks for the help.




Super User I
Super User I

Re: How to get unique values excluding one value - DAX formula?

@cgkas -

Here's the best I could come up with:

Create a table as:

Table = 
SUMMARIZE (
    Table1,
    Table1[ID],
    Table1[Column2],
    "temp", CONCATENATEX (
        FILTER (
            DISTINCT(Table1[Column3]),
            FIND (
                "NOT AVAILABLE",
                Table1[Column3],
                ,
                0
            ) = 0
        ),
        Table1[Column3],
        ", "
    )
)

Column4 as:

Column4 = 
LOOKUPVALUE(
    'Table'[temp],
    'Table'[ID],Table1[ID],
    'Table'[Column2],Table1[Column2]
)

Produces:

5.png





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

cgkas Member
Member

Re: How to get unique values excluding one value - DAX formula?


@ChrisMendoza wrote:

@cgkas -

Here's the best I could come up with:

Is the best you could do, but is the solution I was looking for heheh. Thanks so much for the help.

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors