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 ="[ 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
Community 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"
)
cgkas's avatar
cgkas
Helper V
7 years ago

Hi Matt,

 

This formula it works fine. I only replace

COUNT ( Table1[VALUES] )

to

COUNTROWS ( Table1 )

 since I was getting an error.

 

Only 2 things if possible. The first column of Table1 is "ID". Your formula is applied on a Pivot Table and some IDs have several "correct" and "wrong" values, other IDs doesnt have any value (nor correct not wrong ones). In this case is possible the formula shows empty/nothing? Currently is showing  this:

 

[ Total 1 ]

[ Total ]

 And I'd like for these cases show nothing and if there are correct values but any wrong value I'd like the formula shows like only the total of correct values and nothing below that since wrong values are 0, like this:

[ Total XX ]
... .. ... 

 I hope make sense.

 

Many thanks for the help.