Forum Discussion

mstew55's avatar
mstew55
Frequent Visitor
1 year ago

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 ...
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.