Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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 

"DAX " "DAX help" 'Need help' 'DAX'
"MEASURE"

5 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    1 year ago

    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] )
)

     

     

  • Selva-Salimi's avatar
    Selva-Salimi
    Solution Sage
    1 year ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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 

      • Selva-Salimi's avatar
        Selva-Salimi
        Solution Sage
        1 year ago

        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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.