Forum Discussion

richbenmintz's avatar
richbenmintz
Resident Rockstar
4 years ago

Hi Creative_tree88 ,

 

I think the following measures  will provide a starting point.

Activity Count = CALCULATE(DISTINCTCOUNT('Test Python Exam'[AD_Event_Key]), 'Test Python Exam' = "Activity")

Demand Count = CALCULATE(DISTINCTCOUNT('Test Python Exam'[AD_Event_Key]), 'Test Python Exam' = "Demand")

Hope this helps,

Creative_tree88's avatar
Creative_tree88
Helper V
4 years ago

richbenmintz

 

Thanks so much!  Presume the rest of the statement i.e

 

=count({<Activity_or_Demand={'Activity'},Financial_Year-={'2099'},Room-={'CTSIM','CTSIM2'},Individual_Exam_Parts_Per_Event-={'CLDTH'}>}Event_Key)

 

is done in the same way.  In Qlikview, to exclude a value it is written -= but I'm guessing PowerBi is different?  Having been so used to Qlikview lingo, it's just a case of getting the basics down, then I should be OK.  Appreciate the really quick reply, thanks v much!

  • richbenmintz's avatar
    richbenmintz
    Resident Rockstar
    4 years ago

     

    Hi Creative_tree88 

     

    roughly translated,

    Activity Count = CALCULATE(DISTINCTCOUNT('Test Python Exam'[AD_Event_Key]), 'Test Python Exam' = "Activity", [Financial_Year]<>"2099",Individual_Exam_Parts_Per_Event<>"CLDTH", NOT (Room IN {"CTSIM","CTSIM2"}))

     

     

    • Creative_tree88's avatar
      Creative_tree88
      Helper V
      4 years ago

      richbenmintz 

      Many thanks for your help with this - I've just about got there with it, but now want to calculate an average per month, using the same type of exclusions etc...my syntax is:

       

      Avg Per Month Demand = AVERAGEX(VALUES('Test Python Exam'[AD_CalYrMth]),
      'Test Python Exam' [AD_Data]= "Demand",
      'Test Python Exam'[AD_Examination_01]<>"CLDTH",
      'Test Python Exam'[AD_CalYr]<>2099,
      NOT ('Test Python Exam'[AD_Room] IN {"CTSIM","CTSIM2"}))
      [Count of AD_Event_Key average per AD_CalYrMth])
       
      This doesn't work, surprise surprise...I've managed to get it to work just using the first part i.e. 
       
      Avg Per Month Demand = AVERAGEX(VALUES('Test Python Exam'[AD_CalYrMth]),
      [Count of AD_Event_Key average per AD_CalYrMth])
       
      But when I try to include the 'set analysis' part it falls apart.  Any ideas?  Really appreciate your pointers here.  Kind Rgds
      • richbenmintz's avatar
        richbenmintz
        Resident Rockstar
        4 years ago

        Hi Creative_tree88 

         

        You need to wrapped the averagex inside a calculate like:

        Avg Per Month Demand = 
CALCULATE(AVERAGEX(VALUES('Test Python Exam'[AD_CalYrMth]),[Count of AD_Event_Key average per AD_CalYrMth]),
'Test Python Exam' [AD_Data]= "Demand",
'Test Python Exam'[AD_Examination_01]<>"CLDTH",
'Test Python Exam'[AD_CalYr]<>2099,
NOT ('Test Python Exam'[AD_Room] IN {"CTSIM","CTSIM2"}))
  • Creative_tree88's avatar
    Creative_tree88
    Helper V
    4 years ago

    @richbenmintz

     

    That's got it - just having a little bit of a hang up on the <>2099 part.  I guess it could be down to not recognising the formatting???

    • richbenmintz's avatar
      richbenmintz
      Resident Rockstar
      4 years ago

      Hi Creative_tree88 ,

      If the [financial_year] is a number then remove the Quotes. otherwise what is the error you are receiving?