Distinctcount of column values based on value in another coloumn

[datadog_ab]

Hi PBI Experts, 

Need help with calculating a column. I have a column with name and respective week. Trying to count the distict number of weeks where the count value is one. Table1 shows what we have and trying to make a new Result Table as below.

Table1

Name          Week         Count

John           1/1/23            0

John           1/1/23            1

John           1/2/23            1

Matt           1/1/23            0

Matt           1/2/23            1        

Expected result table below

Result Table

Name       Total Count

John               2

Matt               1

Trying to get the distinct count of weeks  for each person when count = 1 using below query and its resulting in total distinct weeks without filter. 

Total Count = Calculate(Distictcountnoblank(Table1,Table1[Week]), Filter(Table1, Table1[Count] = 1)))

[FreemanZ]

hi @datadog_ab ,

try like:

Measure = 
CALCULATE(
    DISTINCTCOUNTNOBLANK(data[Week]),
    data[count] = 1
)

it worked like:

[datadog_ab]

Trying to get this into a table not measure/visual. I need it as a new column.

[PhilipTreacy]

Hi @datadog_ab 

So what you want to end up with is a new table?  Why do you need to make a new table?

Just asking because unless you really need a table for something, why create it?

Phil

[datadog_ab]

Based on the result column, I will classify it as Medim High Low and use it as a slicer. For ex: 2 is medium 1 is low and 3 is high. I can work around this with measure however there are too many calculations done already based on this result and will end up reworking all those in actual data set.

[PhilipTreacy]

Hi @datadog_ab 

Not following the logic you are using to get your result table.

You say you are looking for the distinct count of weeks where Count is 0.  So wouldn't the result table look like this then?  John only has 1 week (not 2) where Count is 0:

Result Table

Name       Total Count

John               1

Matt               1

Phil

[datadog_ab]

@PhilipTreacy My bad. This supposed to be one not zero. Just corrected question.