Forum Discussion

ewuchatka's avatar
ewuchatka
Icon for Helper II rankHelper II
8 years ago

Count if with existing measure

Hello   I've had almost a year-long break from Power BI  and I've tried to look for the answer on the forum but without any luck. I would appreciate your help.   I am trying to recreate COUNT IF ...
Need Help
ewuchatka's avatar
ewuchatka
Icon for Helper II rankHelper II
8 years ago

Zubair_Muhammad

 

Thank you that's a great step towards what I need, it helps me calculate a total number of Weeks.

 

How can I amend the formula to get to number of weeks where AUP is below 1.10  i.e.  [AUP]<=1.10?

 

Thanks

 

Ewa

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

ewuchatka

 

Try with this

 

Measure =
COUNTX (
    FILTER ( ALLSELECTED ( TableName[Week_Column] ), [AUP Measure] < 1.1 ),
    1
)
  • ewuchatka's avatar
    ewuchatka
    Icon for Helper II rankHelper II
    8 years ago

    Hi guys

     

    Thanks very much :)

     

    I'm almost there.... I think the formula is counting rows and in the data there is more than one row per date as there are different products for each date. So what I need is for the formula to calculate AUP for each date and then count weeks with AUP <1.

     

     

    Thanks

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      8 years ago

      Hi,

       

      Have you tried my formula?  It will count the Week Numbers.

      • ewuchatka's avatar
        ewuchatka
        Icon for Helper II rankHelper II
        8 years ago

         Ashish_Mathur

        Thanks

         

        I'm not sure what I am doing wrong I created a calendar table with three columns (it is the first calendar table I ever created!?):

        Date = CALENDAR (DATE(2000,1,1), DATE(2025,12,31))

        Week Number = WEEKNUM('Date'[Date])

        Year = YEAR('Date'[Date])

         

         

        Then not sure but this formula doesn't work...

        =COUNTROWS(FILTER(SUMMARIZE(Calendar[Week Number],[Week Number],"ABCD",[AUP]),[ABCD]>1))

         

         

        Thanks


        Ewa