Forum Discussion

cgkas's avatar
cgkas
Helper V
7 years ago
Solved

Show unique values and count specific string DAX Formula

Hello to all,

I'm working in Excel 2016.

I have a table with several columns. In column "VALUES" I have something like this:

JJFO
JHD
WRONG
OPDU
GHFRI
WRONG

I currently have this formula

="[ Total " & DISTINCTCOUNT(Table1[VALUES]) & "] 
" & CONCATENATEX(DISTINCT(Table1[VALUES]), Table1[VALUES] ,", ")

that counts distinct values and show the count of them like this:

[ Total 5 ]
JJFO, JHD, WRONG, OPDU, GHFRI

I would like to show the "correct" values and their count and below the "wrong" values and their count like this:

[Total 4 ]
JJFO, JHD, OPDU, GHFRI

[2]
WRONG

 

How can be do this?

 

Thanks for any help.

Need Help
  • MattAllington's avatar
    MattAllington
    7 years ago

    Try this

     

    =
    "[ Total "
    & CALCULATE ( DISTINCTCOUNT ( Table1[VALUES] ), table1[values] <> "wrong" ) & "]"
    & CALCULATE (
    CONCATENATEX ( DISTINCT ( Table1[VALUES] ), Table1[VALUES], ", " ),
    table1[values] <> "wrong"
    ) & "[ Total "
    & CALCULATE ( COUNT ( Table1[VALUES] ), table1[values] = "wrong" ) & "] "
    & CALCULATE (
    CONCATENATEX ( DISTINCT ( Table1[VALUES] ), Table1[VALUES], ", " ),
    table1[values] = "wrong"
    )

5 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    7 years ago

    what is your definition of "wrong".  Is is just the word "wrong"?

    ="[ Total " & calculate(DISTINCTCOUNT(Table1[VALUES]),table1[values]<>"wrong") & "] 
" & calculate(CONCATENATEX(DISTINCT(Table1[VALUES]), Table1[VALUES] ,", "),table1[values]<>"wrong") & 
    "[ Total " & calculate(DISTINCTCOUNT(Table1[VALUES]),table1[values]="wrong") & "] " 
    & calculate(CONCATENATEX(DISTINCT(Table1[VALUES]), Table1[VALUES] ,", "),table1[values]="wrong")

    PLease check for spelling errors and bracket matching.  It should be directionallly correct

    • cgkas's avatar
      cgkas
      Helper V
      7 years ago

      Hi Matt,

       

      Thanks for answer.

      Yes. "wrong" values would be the string "wrong".

       

      Your formula it shows almost correct. It seems to show the unique "correct" and their count correctly but it seems to show always [Total 1] for "WRONG" values even there are more than one "WRONG" strings in the column "VALUES".

       

      Additionally, if possible. If there are no "WRONG" values, may the output show [0 WRONG ]?

       

      Thanks again.

      • cgkas's avatar
        cgkas
        Helper V
        7 years ago
        Someone could help to modify Matt's formula to get count of "wrong" strings?

        Thanks in advance