cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Obtain the second result for the same ID

Hello,

I have a problem that I don't think is very complicated, but I can't seem to solve it.
My data table lists the quality control information carried out in my company. Each inspection is carried out twice by two different people, each with a different job (an 'Rédacteur' and a 'Vérificateur'). These two checks are linked by the same 'ControlId'.
Each of the checks gives a value of either 'Conforme', 'Non conforme' or 'Non applicable', although only the 'Vérificateur' can give a value of 'Non conforme'.
I'd like to know if it's possible to retrieve the agency of the person who is the 'Rédacteur' for whom a 'Vérificateur' has indicated 'Non conforme', which is inside 'Créé par.title', with the aim of obtaining the number of controls for each of the 'Rédacteur' in a matrix, for a specific date filter which I already placed in the report.
If I haven't been clear enough about what I want to do, please don't hesitate to ask me for more information, as I'm not bilingual in English 😅.

1 ACCEPTED SOLUTION
Solution Sage

Create a calculated column to identify 'Rédacteur'

``````IsNonConforme =
IF (
'QualityControl'[Result] = "Non conforme" && 'QualityControl'[Role] = "Vérificateur",
1,
0
)``````

Create a calculated column to link 'Rédacteur' with 'Non conforme'

``````RédacteurAgencyForNonConforme =
CALCULATE (
MAX ( 'QualityControl'[Agency] ),
FILTER (
'QualityControl',
'QualityControl'[ControlId] = EARLIER ( 'QualityControl'[ControlId] ) &&
'QualityControl'[Role] = "Rédacteur" &&
'QualityControl'[IsNonConforme] = 1
)
)``````

Create a measure to count the controls for each 'Rédacteur'

``````CountNonConformeControlsByRédacteur =
CALCULATE (
COUNTROWS ( 'QualityControl' ),
FILTER (
'QualityControl',
'QualityControl'[Role] = "Rédacteur" &&
'QualityControl'[RédacteurAgencyForNonConforme] <> BLANK ()
)
)``````
3 REPLIES 3
Community Support

Hi @SansNom ,

Did @aduguid  response solve your problem? If solved, please mark the helpful answer as a solution this will help more people, if not please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Solution Sage

Create a calculated column to identify 'Rédacteur'

``````IsNonConforme =
IF (
'QualityControl'[Result] = "Non conforme" && 'QualityControl'[Role] = "Vérificateur",
1,
0
)``````

Create a calculated column to link 'Rédacteur' with 'Non conforme'

``````RédacteurAgencyForNonConforme =
CALCULATE (
MAX ( 'QualityControl'[Agency] ),
FILTER (
'QualityControl',
'QualityControl'[ControlId] = EARLIER ( 'QualityControl'[ControlId] ) &&
'QualityControl'[Role] = "Rédacteur" &&
'QualityControl'[IsNonConforme] = 1
)
)``````

Create a measure to count the controls for each 'Rédacteur'

``````CountNonConformeControlsByRédacteur =
CALCULATE (
COUNTROWS ( 'QualityControl' ),
FILTER (
'QualityControl',
'QualityControl'[Role] = "Rédacteur" &&
'QualityControl'[RédacteurAgencyForNonConforme] <> BLANK ()
)
)``````
Frequent Visitor

Thank you, I'm gonna try this now !