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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
STF
Frequent Visitor

Count on a measure

HI All, 

 

I have a running Measure in my pbi where i the out put is put into four seperate catagories. What i would like to have is a count of how many there are of each of the catagories, but i cant seem to get a sum or count fucntion that works. 

 

CALCULATE(
SWITCH( TRUE(),
[Avg. Days Untill Contract Expires] > 1095, "> 3 years",
AND([Avg. Days Untill Contract Expires] <= 1095, [Avg. Days Untill Contract Expires] > 730), "2-3 years",
AND([Avg. Days Untill Contract Expires] <= 730, [Avg. Days Untill Contract Expires] > 365), "1-2 years",
AND([Avg. Days Untill Contract Expires] <= 365, [Avg. Days Untill Contract Expires] > 180), "< 1 year",
AND([Avg. Days Untill Contract Expires] <= 180, [Avg. Days Untill Contract Expires] > 0), "< 6 month",
ISBLANK([Avg. Days Untill Contract Expires]) , "None"),
ALL('Date')
)

 

Thanks in advance 

5 REPLIES 5
Anonymous
Not applicable

Hi @STF ,

 

May I ask if you have gotten this issue resolved? If it is solved, please mark the helpful reply or share your solution and accept it as solution.
If not resolved please provide example data. We can better understand the problem and help you.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Dengliang Li

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

Selva-Salimi
Super User
Super User

Hi @STF 

 

What do you want to count this measure based on? Is that based on your contracts? if so, you can write another measure as follow: (lets think that your previous measure is called measure 1)

 

measure 2 := var tbl= summarize(your_table , contract , measure1)

return

countx (filter (tbl , contract = selectedvalue (contract) ) , measure 1)

 

if this measure does not cover your expectations, please share more details.

 

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly. 

Hi Selva! 

 

Its close to the solution i would like, however not quite. What i would like to have the count of is each of the defined catagories, i.e how mayn rows have the "2-3 years" as a value. 

The problem im facing is that the above mesaure cannot be used as a catagory table for the count, but also that [Avg. Days Untill Contract Expires] is a mesure that is under a different table. 

Hope this shines a bit more light on what im looking for :). 

Mvh 

Stf 

@STF 

 

If you define your rows in the summarized table in the measure I recommend, then you can count each category.

 

please share more detail of the visual you want to have on your dashboard and also dummy samples 

DataInsights
Super User
Super User

@STF,

 

One approach is to create a category table such as the one below:

 

DataInsights_0-1727355082529.png

 

Example measure:

 

Category Count = 
CALCULATE (
    COUNTROWS ( Contract ),
    Contract[Days to Expiration] > SELECTEDVALUE ( Category[Lower Bound] ),
    Contract[Days to Expiration] <= SELECTEDVALUE ( Category[Upper Bound] )
)

 

DataInsights_1-1727355120581.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.