cancel
Showing results for
Did you mean:
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.

 Period Location Tests Design Evaluation Practicability evaluation 6 Location 1 Test 1 satisfactory satisfactory 6 Location 2 Test 2 satisfactory satisfactory 6 Location 3 Test 3 unsatisfactory unsatisfactory 6 Location 4 Test 4 satisfactory satisfactory 6 Location 5 Test 5 satisfactory unsatisfactory 6 Location 6 Test 6 satisfactory satisfactory 6 Location 7 Test 7 satisfactory satisfactory 6 Location 8 Test 8 satisfactory satisfactory 6 Location 9 Test 9 unsatisfactory unsatisfactory 6 Location 10 Test 10 satisfactory satisfactory 6 Location 11 Test 11 unsatisfactory unsatisfactory 6 Location 12 Test 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.

1 ACCEPTED SOLUTION
Community Support

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

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

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.
Helper IV

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:

 Period Location Tests Referred testing Referral status Design Evaluation Practicability evaluation 6 Location 1 Test 1 satisfactory satisfactory 6 Location 2 Test 2 satisfactory satisfactory 6 Location 3 Test 3 unsatisfactory unsatisfactory 6 Location 4 Test 4 satisfactory satisfactory 6 Location 5 Test 5 satisfactory unsatisfactory 6 Location 6 Test 6 satisfactory satisfactory 6 Location 7 Test 7 satisfactory satisfactory 6 Location 8 Test 8 satisfactory satisfactory 6 Location 9 Test 9 unsatisfactory unsatisfactory 6 Location 10 Test 10 satisfactory satisfactory 6 Location 11 Test 11 unsatisfactory unsatisfactory 6 Location 12 Test 12 - - 6 Location 13 Test 5 Testing refered to Location 5 Accepted

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.

Mous.

Community Support

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 (
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.
Helper IV

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?

Community Support

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

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.
Helper IV

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

Community Support

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.
Helper IV

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

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks