Calculate the number of rows that has an exact value for distinct ids

[Rihab]

Hello, 

i am new in power bi world and I would like to create a measure that counts how many Rows had the value to investigate but for distinct Id , means if we have 3 rows with ´to investigate "value it should be counted as 1. Here is an example of data , the measure should show 3 as a result 

thank you in advance

[bhanu_gautam · Accepted solution]

@Rihab , you can try using below measure

DistinctCountToInvestigate =
CALCULATE(
DISTINCTCOUNT(YourTable[Id]),
YourTable[Value] = "to investigate"
)

Replace value as 3 and mention your table name it will work

[manvishah17]

Hi @Rihab , 
In my sample data 'to Investigate' is 4 time so...
for only 'To investigate' , you can use this 

[Rihab]

 the 3 rows of value "to investigate" for ID 1 should be counted as 1 , is there possible to do it without using filters , it should be in the measure , also in your first response you are counting on ID, by my goal to count the value "to investigate" 

[manvishah17]

Okay @Rihab ,
I got your point, you can count on values, but for choosing only one "to investigate", you need to choose it from filter whether it can  be a slicer , or visual level filter.

Count = 
 CALCULATE(
    DISTINCTCOUNT(Sheet2[value])
 )

[jay_patel]

Just add visual level filter for value = "To investigate". it will gives you distinct count for that only.

[manvishah17]

Hi @Rihab ,
In order to count distinct id you can use this measure ,

Count = 
DISTINCTCOUNT(Sheet2[id])

See I have created sample file and it shows following results ,

 If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Rihab]

thank you for your quick response but I need only to calculate only when the value is "to investigate" and the KPI should show 3 not 4