Using a lookup table to compare data with a specific comparator group.

[Anonymous]

I am fairly new to PowerBI, I have (I hope) a fairly simple problem. I have a fact table with some test results, connected to a "Name" table, that takes the following form, and it basically has students names, there region and a specific comparator group. 

Name	Region	Comp Group
Steve	N	N1
Sarah	S	N2
Will	W	N3
Phil	N	N4
Caroline	S	N5

Now, I can filter the test data by Student, Year (when connected to a calendar table), and region all easily using slicers. But, I want to be able to filter by comparator groups N1-N5, where they are related to the students by the following table 

I am not sure how to do this, but my thinking was to introduce a slicer that lets me choose either region or comp group and if I chose either it then filters by those results based on the first selection, so fo example I can do this with region with the following measure: 

CG choice (region) = if(

SELECTEDVALUE(CG[CG])="Region",SUM('Fact'[Value]),"null"

) 

and the graphs on the page then update based on my selections and all is fine. However, there needs to be some form of lookup with the comp group selection becuase each group N1-N5 is related to a list of names asd follows 

Comp Group	Original Name	NN1	NN2
N1	Steve	Will	Sarah
N2	Sarah	Will	Phil
N3	Will	Phil	Caroline
N4	Phil	Caroline	Will
N5	Caroline	Phil	Sarah

Please can someone advide me how to write a dax code that if I chose Comp Group in a filter, then looks up the name I chose to the orginal name column in this table and returns the original name, NN1 and NN2. And then, filters the graph by these names. So for example if I chose Will, in one slicer, then chose Comp group on the next slicer, then the graphs will update to show the test scores of Will, Phil and Caroline only.  

Thanks.

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, I create a sample, this is the Test table.

Comp Group table is the same with you, but don't make relationship with other tables.

Here's my solution, create a measure.

Check =
IF (
    ISFILTERED ( 'Comp Group'[Comp Group] ),
    IF (
        MAX ( 'Test'[Name] ) = SELECTEDVALUE ( 'Comp Group'[Original Name] )
            || MAX ( 'Test'[Name] ) = SELECTEDVALUE ( 'Comp Group'[NN1] )
            || MAX ( 'Test'[Name] ) = SELECTEDVALUE ( 'Comp Group'[NN2] ),
        1,
        0
    ),
    1
)

Put the measure in the visual filter and select its value to 1.

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Thanks that is aexactly what I am after and I have scaled it up to my big database. I just had two queries: (1) could you explain what the || symbols mean here, I tried to google it but couldn't fine the answer, and (2) could you explain why the max was used? - is it because you only want to find the comp group names for one authority only, and the one selected in the filter?  

[v-yanjiang-msft]

Hi @Anonymous ,

Glad your problem is solved!

1. || is the OR operator, see the document: OR function (DAX) - DAX | Microsoft Docs

2. The MAX function in a measure always return the current row of the column, because we can't directly reference a column in a measure, so we should add MAX/MIN/SUM before the column.

Best Regards,
Community Support Team _ kalyj

[amitchandak]

@Anonymous , Based On what I got, you want to compare two selected comp groups. If so, refer to my approch in the video

Compare two Brands: https://youtu.be/exN4nTewgbc