Forum Discussion

cgkas's avatar
cgkas
Icon for Helper V rankHelper 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 ="[ Tot...
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"
    )
MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity 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
    Icon for Helper V rankHelper 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
      Icon for Helper V rankHelper V
      7 years ago
      Someone could help to modify Matt's formula to get count of "wrong" strings?

      Thanks in advance
      • MattAllington's avatar
        MattAllington
        Icon for Community Champion rankCommunity Champion
        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"
        )