topic Re: Count on a measure in DAX Commands and Tips

Count on a measure

Anonymous — Thu, 26 Sep 2024 10:42:44 GMT

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

Re: Count on a measure

DataInsights — Thu, 26 Sep 2024 12:52:44 GMT

Anonymous,

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

Example measure:

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

Re: Count on a measure

Selva-Salimi — Thu, 26 Sep 2024 12:58:11 GMT

Hi Anonymous

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.

Re: Count on a measure

Anonymous — Fri, 27 Sep 2024 08:15:23 GMT

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

Re: Count on a measure

Selva-Salimi — Fri, 27 Sep 2024 11:09:02 GMT

Anonymous

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

Re: Count on a measure

Anonymous — Tue, 08 Oct 2024 08:31:53 GMT

Hi Anonymous ，

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.