DAX formula help

[ramhariessentia]

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

[v-binbinyu-msft]

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]

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

[v-binbinyu-msft · Accepted solution]

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.

[ramhariessentia]

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.