Skip to main content
cancel
Showing results for 
Search instead 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

Reply
SansNom
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 😅.
Thank you in advance for your reply!

1 ACCEPTED SOLUTION
aduguid
Solution Sage
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 ()
    )
)

View solution in original post

3 REPLIES 3
v-tangjie-msft
Community Support
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. 

aduguid
Solution Sage
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 ()
    )
)

Thank you, I'm gonna try this now !

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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