Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

powerbi.png

Articles[CODE] is related many-to-one with Rules[CODE], because Articles can have several codes.

Each code is related to a group. Group values are either 1, 2 or 3.

There is a hierarchy between groups ; if article XX9EODS has two codes : AAA -> group = 1 and BBB -> group = 2 then Articles[GROUP] will be 1. Articles can only have one group.

 

With these information, I imagine to get all codes related to one article, then compare it to the codes in the Rules table, then get the min of the selected groups in the Rules table and finally compare this min to the one related to the article and return 1 if correct and 0 if not. And do this for each article.

 

Here is one example:

powerbi.png

 

Should I use LOOKUPVALUES(), SUMMARIZECOLUMNS(), etc. ?

I think I have not enough DAX knowledge to achieve this. Could you help me? 

 

Thanks!

 

 

3 REPLIES 3
AlB
Super User
Super User

@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 

SU18_powerbi_badge

Anonymous
Not applicable

Example added, with correct group values in green and wrong group values in red.

AlB
Super User
Super User

Hi @Anonymous 

Can you show an example based on data to help clarify the requierement please?

Please mark the question solved when done and consider giving kudos if posts are helpful.

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

Cheers 

SU18_powerbi_badge

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors