Forum Discussion

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

        Thank you so much!  So close...yet so far.  Really appreciate your help.  All the best.