Forum Discussion

ramhariessentia's avatar
ramhariessentia
Helper I
4 years ago
Solved

DAX formula help

Hi all,

I have a requirement to find out the number of cases requiring inspection based on the number of cases submitted by each account.

eg.

Lets say i have got 90 cases from an account, I need to count 1 for every 25 cases, so in this case, I should get a value of 3.

and I also need to use this count in a master inspection criteria which has other conditions (a group of or conditions) where the system checks many criteria & all criteria fail then the system checks if it is the 25th case & previous 24 were also no inspected it will call for an inspection.

 

I use the following formula for defining my other criteria.

Master Inspection Criteria = IF('Case'[Substation80Capacity__c]=True||'Case'[Switchboard_approval_required__c]=True||'Case'[TotalSiteEGBCapacity__c]>30||'Case'[IsBatteryStorageInstalled__c]=true||'Case'[RelatedContestableProjectNum__c]<>blank()||'Case'[NonDomesticEquipmentRequired__c]="yes"||'Case'[Export limit criticality]="Rural 3 to 10"||'Case'[Export limit criticality]="Rural 10 to 20"||'Case'[Export limit criticality]="Rural 20 to 30","Inspection required","No Inspection required")
 
I want to add a criteria to check if its a 25th case without inspection
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi ramhariessentia ,

    For me, it is difficult to give a perfect solution based on your formula by imagination alone. I have tried my best to give an answer close to your needs, but in your actual production environment, you need to adjust it to your actual situation, and I hope the solution given this time will be useful to you.

     

    I just adjust the DAX formula according to your demands, add a new column by below formula

    Column 2 =
VAR cur_indexbyapp = 'Case'[index_by_app]
VAR cur_app = 'Case'[Applicant]
VAR tmp =
    FILTER ( 'Case', 'Case'[Applicant] = cur_app && 'Case'[Inspection] = "no" )
VAR ctn =
    COUNTROWS ( tmp )
RETURN
    SWITCH ( TRUE (), ctn = 2, "yes", BLANK () )

    Please refer attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • ramhariessentia's avatar
    ramhariessentia
    Helper I
    4 years ago

    This is the count of cases per applicant, i need a rule which ensures that a case is requiring inspection if it is the 25th case after a previous case that had an inspection.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi ramhariessentia ,

    Please try below steps:

    1. I create a table to test

    Case:

    2. add a index column in Power Query, then add a other column that  group by the "Applicant" to create a index

    index_by_app =
VAR cur_app = 'Case'[Applicant]
VAR tmp =
    FILTER ( 'Case', 'Case'[Applicant] = cur_app )
RETURN
    RANKX ( tmp, [Index],, ASC )

    3. my calculate logic is that if the  index_by_app=2 and the inspection="yes", then the index_by_app=3 should  be "yes".

    add acolumn:

    Column =
VAR cur_indexbyapp = 'Case'[index_by_app]
VAR cur_app = 'Case'[Applicant]
VAR inspect =
    CALCULATE (
        MAX ( 'Case'[Inspection] ),
        FILTER ( 'Case', 'Case'[index_by_app] = 2 && 'Case'[Applicant] = cur_app )
    )
RETURN
    SWITCH (
        TRUE (),
        cur_indexbyapp <> 3, 'Case'[Inspection],
        inspect = "yes", "yes"
    )

    Please refer my attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ramhariessentia's avatar
      ramhariessentia
      Helper I
      4 years ago

      Thanks heaps, the solution  needs little bit tweaking i guess,  my other formula is this

       

      Master Inspection Criteria = IF('Case'[Substation80Capacity__c]=True||'Case'[Switchboard_approval_required__c]=True||'Case'[TotalSiteEGBCapacity__c]>30||'Case'[IsBatteryStorageInstalled__c]=true||'Case'[RelatedContestableProjectNum__c]<>blank()||'Case'[NonDomesticEquipmentRequired__c]="yes"||'Case'[Export limit criticality]="Rural 3 to 10"||'Case'[Export limit criticality]="Rural 10 to 20"||'Case'[Export limit criticality]="Rural 20 to 30","Inspection required","No Inspection required").
       
      Based on your code i created following code.
      Count+Master Critieria =
      var cur_indexbyaccount='Case'[Index_by_Account]
      var cur_account='Case'[AccountId]
      var inspect=CALCULATE(MAX('Case'[Master Inspection Criteria]),FILTER('Case','Case'[Index_by_Account]=2 &&'Case'[AccountId]=cur_account))
      RETURN
      switch(true(),
      cur_indexbyaccount<>25,'Case'[Master Inspection Criteria],
      inspect="Inspection required","Inspection required").

      can you please twaek this code, because  still using the latest formula solution is not giving required result,
      for an account 24 cases were not requireing inspection then 25 th should be inspected
       
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi ramhariessentia ,

        For me, it is difficult to give a perfect solution based on your formula by imagination alone. I have tried my best to give an answer close to your needs, but in your actual production environment, you need to adjust it to your actual situation, and I hope the solution given this time will be useful to you.

         

        I just adjust the DAX formula according to your demands, add a new column by below formula

        Column 2 =
VAR cur_indexbyapp = 'Case'[index_by_app]
VAR cur_app = 'Case'[Applicant]
VAR tmp =
    FILTER ( 'Case', 'Case'[Applicant] = cur_app && 'Case'[Inspection] = "no" )
VAR ctn =
    COUNTROWS ( tmp )
RETURN
    SWITCH ( TRUE (), ctn = 2, "yes", BLANK () )

        Please refer attached .pbix file.

         

        Best regards,
        Community Support Team_ Binbin Yu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.