Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Get max value from related table list

Hello all,   In a data quality control project, I want to compare two groups, one entered by a user and the other defined by a rule. Articles[CODE] is related many-to-one with Rules[CODE], b...
AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous 

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Try the following:

1. Delete the relationship between the two tables

2. Place Article, Code and Group of the Articles table in a table visual (all set no "Don't summarize", so that it shows exactly as in your pic above)

3. Create this measure and place it in the visual:

 

Measure =
VAR articleCodes_ =
    CALCULATETABLE (
        DISTINCT ( Articles[Code] ),
        ALLEXCEPT ( Articles, Articles[Code] )
    )
VAR correctCode_ =
    MINX ( FILTER ( Rules, Rules[Code] IN articleCodes_ ), [Group] )
VAR currentCode_ =
    SELECTEDVALUE ( Articles[Code] )
RETURN
    IF ( currentCode_ = correctCode_, 1, 0 )

 

All this can also be done in the Articles table itself, by adding a calculated column. And it can be done as well in Power Query, which would probably be the best option

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers