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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sparkizone
Frequent Visitor

Count the value based on condition

Hi Everyone,

 

I've tried to count the number of 'Providers' by Measure conditions but no luck last few days. 

Can you please share your ideas?

 

 

So, a column and measures are like

 

Table name: Table 1

Column1 : Providers

Column2: Service Start Date

 

Providers

Service Start Date

A

22/03/2022

B

14/03/2022

C

----

C

----

----

----

 

Table name: Table 2 

Column1: Providers

Column2: Total Contracted Services

 

Providers

Total Contracted Services

A

16

B

50

C

98

D

5

---

---

 

Measures:

Target monthly = 

DIVIDE( 'Table2' [Total Contracted Services],12 )

 

CurrentFYMonth =

IF ( MONTH( TODAY() ) < 7, MONTH ( TODAY () ) + 6, MONTH ( TODAY () ) - 6 )

 

Target Services =

[Target monthly] * ([CurrentFYMonth])

 

Tracking A =  COUNTA('Table1'[Service Start Date]) - [Target Services]

Tracking B =  (Measure) -> Tracking A >= 0

 

 

I can make a table in Power BI as below in visualisation,

 

ProvidersTracking ATracking B
A7True
B12True
C-3False
D56True
E-44False
F0True
---------

 

I want to count the number of providers with True and False separately.

Like 46 providers on Track(True), 15 providers are below ----

 

Thanks,

 

 

 

 

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Define the question in simple English, share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello Ashish,

 

I've shared a sample data, and power bi file.

https://drive.google.com/drive/folders/1vwr8VnCAHtILKo9YrNz5znnz2-q2x_2f?usp=sharing

 

In short, I want to count how many distinct providers from both True and False to present/define either the number of on track proivders or not in Power BI.

For intance, within the data, I want to present "2 Providers are on Track", and "3 providers are below"

 

Hoping it makes sense. 

Thanks,

 

 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks for your help!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User
Super User

@Sparkizonebasically you are looking for dynamic segmentation, check this post and tweak it as you see fit Dynamic segmentation – DAX Patterns

 

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors