Forum Discussion
Count Unique Values based on another column, repeat value rows
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
12 Replies
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/1447523Hi 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.
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 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.
Based on my example, it should be 1 client with No sales
Sorry 2 clients (Amber and Lacy)
Awesome Sauce!!!! WOrks perfectly! Is it possible to do it in a column, to indicate which client by row, did not have a sale?
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.
Good Morning Yilong Zhou, I'm getting an error trying to use the above in calculated column
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.
Would this work in Power Pivot? also what if i wanted to add a date criteria? Example Greater than Date1 and less than Date2
