Count Unique Values based on another column, repeat value rows

[mstew55]

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

[mstew55]

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

[mstew55]

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

[Anonymous]

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.

[mstew55]

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

[Anonymous]

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.

[mstew55]

Sorry 2 clients (Amber and Lacy)

[FreemanZ]

hi @mstew55 ,

try like:

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

it works like:

[mstew55]

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

[mstew55]

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. 

[mstew55]

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

[Anonymous]

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.

[lbendlin]

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-...

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/1447...