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 t...
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.

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.