topic Re: Count Unique Values based on another column, repeat value rows in DAX Commands and Tips

Count Unique Values based on another column, repeat value rows

mstew55 — Thu, 17 Oct 2024 16:47:07 GMT

Hello, i have one table. Same client name can repeat 50 times, but different records based on other rows. How can count the number of clients, with all blank rows in another column? some clients can have blank rows and non blank rows. But i want to see clients with all blank rows and no non blank rows in another column

Re: Count Unique Values based on another column, repeat value rows

lbendlin — Thu, 17 Oct 2024 21:04:52 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Count Unique Values based on another column, repeat value rows

Anonymous — Fri, 18 Oct 2024 02:57:47 GMT

Hi mstew55 ,

I create a table as you mentioned.

Next I create a calculated column.

IsBlankColumnB = IF('Table'[ColumnB]=BLANK(), 1, 0)

Then I create a new table and a calculated column.

ClientSummary = SUMMARIZE( 'Table', [ClientName], "TotalRows", COUNTROWS('Table'), "BlankRows", SUM('Table'[IsBlankColumnB]) )

AllBlankRows = IF([TotalRows] = [BlankRows], 1, 0)

Finally I think you can create another measure and it will give you what you want.

CountAllBlankClients = CALCULATE( COUNTROWS(ClientSummary), ClientSummary[AllBlankRows] = 1 )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count Unique Values based on another column, repeat value rows

mstew55 — Fri, 18 Oct 2024 12:25:11 GMT

Client	Product	Sold
Chris	Strawberry	y
John	blackberry	y
Amber	Cherrys
Todd	Peanuts	y
Chris	Hotdogs
Chris	hamburger
John	Hotdogs
Todd	cookies
Amber	Granite
Bill	Icecream	y
Lacy	Walnuts
benny	chairs	y

Re: Count Unique Values based on another column, repeat value rows

mstew55 — Fri, 18 Oct 2024 12:26:04 GMT

As you can see some clients have mutilple products, with sales and no sales. I need to identify clients with no sales regardless of product.

Re: Count Unique Values based on another column, repeat value rows

mstew55 — Fri, 18 Oct 2024 12:34:55 GMT

Based on my example, it should be 1 client with No sales

Re: Count Unique Values based on another column, repeat value rows

mstew55 — Fri, 18 Oct 2024 12:35:41 GMT

Sorry 2 clients (Amber and Lacy)

Re: Count Unique Values based on another column, repeat value rows

FreemanZ — Fri, 18 Oct 2024 15:21:19 GMT

hi mstew55 ,

try like:

measure = COUNTROWS( FILTER( ADDCOLUMNS( VALUES(data[client]), "@sold", CALCULATE(MAX(data[sold])) ), [@sold]=BLANK() ) )

it works like:

Re: Count Unique Values based on another column, repeat value rows

mstew55 — Fri, 18 Oct 2024 17:12:28 GMT

Awesome Sauce!!!! WOrks perfectly! Is it possible to do it in a column, to indicate which client by row, did not have a sale?

Re: Count Unique Values based on another column, repeat value rows

Anonymous — Mon, 21 Oct 2024 03:06:35 GMT

Hi mstew55 ,

I think you could try creating a new table to accomplish what you need.

Firstly I create a table as you mentioned.

Then you can choose to create a new table and here is the DAX code.

NewTable = VAR _ClientsWithSales = SUMMARIZE( FILTER( 'Table', 'Table'[Sold] = "y" ), 'Table'[Client] ) RETURN DISTINCT( FILTER( 'Table', NOT('Table'[Client] IN _ClientsWithSales) ) )

Again you can choose the form you want under Report view.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count Unique Values based on another column, repeat value rows

mstew55 — Mon, 21 Oct 2024 12:36:01 GMT

Good Morning Yilong Zhou, I'm getting an error trying to use the above in calculated column

Re: Count Unique Values based on another column, repeat value rows

Anonymous — Tue, 22 Oct 2024 02:49:43 GMT

Hi mstew55 ,

Like I mentioned in my earlier reply.

This is not a Calculated column, it is recreating a new table.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count Unique Values based on another column, repeat value rows

mstew55 — Fri, 25 Oct 2024 19:13:55 GMT

Would this work in Power Pivot? also what if i wanted to add a date criteria? Example Greater than Date1 and less than Date2