Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mous007
Helper IV
Helper IV

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

1 ACCEPTED SOLUTION

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]
        )
    )

 

1.PNG

 


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.

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.

View solution in original post

8 REPLIES 8
v-lid-msft
Community Support
Community Support

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"
)

 

2.PNG


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.

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.

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.

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.

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.

Thank you again @v-lid-msft for your prompt answers.

 

I have tried the code and modified my table names but It is giving me the same count as the previous formula I have used before.

 

NB; after i created the columns, i just added measures with calculate n count expressions.

 

Any hint on how i should handle this?

 

Thanks in advance

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]
        )
    )

 

1.PNG

 


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.

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.

@v-lid-msft the column is working just fine now, thanks again.

 

I just have one more small request if you can assist me with please, the newly created column now shows both calculations but how can i modify the code to only show on the column ( or count in the measure) the non Valid by referencing only ?

 

I played around with the code but as a rooky i could not get the result i wanted.

 

I appreciate your assiatance again.

 

 

Hi @Mous007 ,

 

We can try to create measure using following formula to meet your requirement:

 

TotalCountByRefer =
COUNTROWS (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[ValidStatus] = "Non Valid"
            && [Referral status] = "Accepted"
    )
)

 

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.

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.

Hi @v-lid-msft , the measure is giving me the correct total ( 252 for example for October) but it is giving the same count 252 for each test.

 

should i add calculate or any other dax to my measure to correct it?

-------- Edit --------

 

Hi @v-lid-msft again, im editing this post to let you know i managed a little dax on my own to get the results i wanted.

 

I simply used CALCULATE(COUNTA('table'[Valid referencing Status]), 'First Sheet'[Valid referencing Status] = "non valid by reference") 

 

Many thanks again for your help.

 

Mous

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.