Forum Discussion

Mous007's avatar
Mous007
Helper IV
6 years ago
Solved

Calculate Count IF selection rule

Hello,

 

Below is a sample of my data. I have several tests that are tested through out the year. I have another mapping table for the tests to be tested/run for each month. My monthly data set include all the tests and location whether they are testd during that month or not but I also have my mapping table to deal wih that.

 

PeriodLocationTestsDesign EvaluationPracticability evaluation
6Location 1Test 1satisfactorysatisfactory
6Location 2Test 2satisfactorysatisfactory
6Location 3Test 3unsatisfactoryunsatisfactory
6Location 4Test 4satisfactorysatisfactory
6Location 5Test 5satisfactoryunsatisfactory
6Location 6Test 6satisfactorysatisfactory
6Location 7Test 7satisfactorysatisfactory
6Location 8Test 8satisfactorysatisfactory
6Location 9Test 9unsatisfactoryunsatisfactory
6Location 10Test 10satisfactorysatisfactory
6Location 11Test 11unsatisfactoryunsatisfactory
6Location 12Test 12--

 

I have a measure that calculates the satisfactory/ unsatisfactory status of each test for Design and Practicality evaluation individually and it is working fine.

 

Now what I want to do is assess the NON VALIDTY of the tests and one the following conditions have to apply in order for a test to be counted as Non Valid IF one of the rules applies to each individual test:

 

1) first rule: Design Evaluation = unsatisfactory first and Practicability evaluation does not even have to be tested because the first part of the evaluation was unsatisfactory.

 

2) second rule: Design Evaluation = satisfactory than I want to check if Practicability evaluation = unsatisfactory and then I should count it as Non valid.

 

The issue I have with my own independ measures testing for design and Practicability individually is that I am getting tests to be counted twice and I cannot find a solution for this decision rule so I can evaluate my tests for both conditions but only once.

 

I know my descrption for the problem might be confusing but please let me know if I can provide you with more details.

 

Thanks in advance

Need Help
Tutorial Requests
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Mous007 ,

     

    Sorry for that there are some mistake in my previous formula, please try to use the new one:

     

    ValidStatus = 
VAR t = [Tests]
VAR r = [Referred testing]
RETURN
    IF (
        [Referral status] <> "Accepted",
        IF (
            [Design Evaluation] = "unsatisfactory"
                || ( [Design Evaluation] = "satisfactory"
                && [Practicability evaluation] = "unsatisfactory" ),
            "Non Valid"
        ),
        MAXX (
            ADDCOLUMNS (
                FILTER (
                    'Table',
                    'Table'[Tests] = t
                        && 'Table'[Location] = SUBSTITUTE ( r, "Testing refered to ", BLANK () )
                ),
                "Status", IF (
                    [Design Evaluation] = "unsatisfactory"
                        || ( [Design Evaluation] = "satisfactory"
                        && [Practicability evaluation] = "unsatisfactory" ),
                    "Non Valid"
                )
            ),
            [Status]
        )
    )

     

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Mous007 ,

     

     We can create a calculated column use following formula based on  your rules:

     

    ValidStatus =
IF (
    [Design Evaluation] = "unsatisfactory"
        || ( [Design Evaluation] = "satisfactory"
        && [Practicability evaluation] = "unsatisfactory" ),
    "Non Valid"
)

     


    If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Mous007's avatar
      Mous007
      Helper IV
      6 years ago

      Thank you very much v-lid-msft , it is working perfectly for me. 

       

      I have another little addition to my original query. 

       

      Let's assume my new data looks like this:

       

      PeriodLocationTestsReferred testingReferral statusDesign EvaluationPracticability evaluation
      6Location 1Test 1  satisfactorysatisfactory
      6Location 2Test 2  satisfactorysatisfactory
      6Location 3Test 3  unsatisfactoryunsatisfactory
      6Location 4Test 4  satisfactorysatisfactory
      6Location 5Test 5  satisfactoryunsatisfactory
      6Location 6Test 6  satisfactorysatisfactory
      6Location 7Test 7  satisfactorysatisfactory
      6Location 8Test 8  satisfactorysatisfactory
      6Location 9Test 9  unsatisfactoryunsatisfactory
      6Location 10Test 10  satisfactorysatisfactory
      6Location 11Test 11  unsatisfactoryunsatisfactory
      6Location 12Test 12  --
      6Location 13Test 5Testing refered to Location 5Accepted  

       

      I have a new Location 13 which is also testing Test 5, so they made a referral for the testing which means that Location 13 evaluation should follow whatever the evaluation result (from newly created column as u suggested) from Test 5 in Location 5. I believe this can be understood as a Vlookup or indexnmatch function. 

       

      Any suggestions would be more than welcome.

       

      Thanks in advance.

       

      Mous.

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Mous007 ,

         

        We can try to create a calculated column use following formula to meet your requirement, please replase the 'Table' with your table name: 

         

        ValidStatus =
VAR t = [Tests]
VAR l = [Location]
RETURN
    IF (
        [Referral status] <> "Accepted",
        IF (
            [Design Evaluation] = "unsatisfactory"
                || ( [Design Evaluation] = "satisfactory"
                && [Practicability evaluation] = "unsatisfactory" ),
            "Non Valid"
        ),
        MAXX (
            ADDCOLUMNS (
                FILTER (
                    'Table',
                    'Table'[Tests] = t
                        && 'Table'[Location] = SUBSTITUTE ( [Referred testing], "Testing refered to", "" )
                ),
                "Status", IF (
                    [Design Evaluation] = "unsatisfactory"
                        || ( [Design Evaluation] = "satisfactory"
                        && [Practicability evaluation] = "unsatisfactory" ),
                    "Non Valid"
                )
            ),
            [Status]
        )
    )

         

         

        Best regards,

        Community Support Team _ Dong Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.