Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
luisy93
Frequent Visitor

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 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!

12 REPLIES 12
amitchandak
Super User
Super User

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

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

Can you share some sample data and expected output

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. 

Activities - workshop.GIF

 

am I making it clear? 

Thanks for your response.

Hi @luisy93 ,

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 )

1.PNG

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.

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.

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.

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

 

Hi @luisy93 ,

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.

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.

error.GIF

I attach the image again and a sample file.

Sample

Thank you for your response



Hi @luisy93 ,

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.

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.

Hello, 

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

thanks

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.