Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cgkas
Helper V
Helper V

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.

1 ACCEPTED SOLUTION

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"
)


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

5 REPLIES 5

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

Someone could help to modify Matt's formula to get count of "wrong" strings?

Thanks in advance

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"
)


* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.