Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

conditional count

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 ...
Need Help
v-xuding-msft's avatar
v-xuding-msft
Community Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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

 
  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago


      I attach the image again and a sample file.

      Sample

      Thank you for your response

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        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