conditional count

[Anonymous]

Hello everybody,

I have a table that brings information from CRM about workshops the users do.

In reality, these workshops can't be more than two per day. And if there is more, it is a mistake.

I want to show in a table the correct number. So I tried this: 

- New column: Concatenate Date Workshop=  if ( 'Activities'[ type of meeting] = "Workshop" ; 'Activities'[scheduledend].[Date] & 'Activities'[type of meeting])

- Measure: Corrected number =

Var Count  workshop = CALCULATE(COUNTA('Activities'[Concatenate Date Workshop]))
Return
CALCULATE(if(Count Workshop > 2;1;Count Workshop))

But it is not working. 
have a great day!

[amitchandak]

is the filter of the workshop not working. Are you able to use distinctcount ?

[Anonymous]

I thought about using distinct count, but i need to find equals (same date and qualified as a workshop). 

[amitchandak]

Can you share some sample data and expected output

[Anonymous]

I attach a sheet with the information that power BI takes from CRM.

The rows painted in yellow should count as two. but, the orange ones, should count as one. Because is >2

That's why I can't just use DISTINCTCOUNT. 

am I making it clear? 

Thanks for your response.

[v-xuding-msft]

Hi @Anonymous ,

I create a simple sample. Please try and check if it is what you want.

Measure = 
VAR _count =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Subject] ),
            'Table'[Activity type code] = "Workshop"
        )
    )
RETURN
    IF ( _count <= 2, _count, 1 )

For more details, please see the attachment.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello, 

I checked this again. I used that formula and it is okay. but see what happens in the final result row: it doesn't sum (check it with your sample table)

and what I need is a final number of activities sum, which has been filtered by this conditional.

am I making myself clear?  please let me know if I am not.

Thank you so much for your answer

Regards.

[Anonymous]

i tried the exact formula, but i got this error message:

[v-xuding-msft]

Hi @Anonymous ,

Can you please share the error message again? The photo is missing.  And if you could share a dummy pbix file, we will understand the actual situation clearly and solve it quickly.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I attach the image again and a sample file.

Sample

Thank you for your response

---

---

[v-xuding-msft]

Hi @Anonymous ,

Sorry for late back. Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here. If you are still confused, please share a dummy pbix file rather than a report from service.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello, 

I still have this problem. Because I can't sum these values. 

thanks

[amitchandak]

Looking at the data screenshot, you posted earlier. Looking at yellow and orange, I can not make a logic when to count and when to distinctcount. But assume you have such condition that can give difference 

then try a new measure

measure =
var _cnt = calculate(count(table[subject]),table[subject] = "???")
var _discnt = calculate(distinctcount(table[subject]),table[subject] <> "???")
return
_cnt + _discnt