Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thanks in advance
Solved! Go to 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]
)
)
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.
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.
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.
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.
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]
)
)
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.
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |