Find rank of external data point in filtered query

[brspencer90]

Hi all - 

Am looking to try to find a rank of a data point filtered in a query (Q1) in a separate query with separate filters (Q2).

Due to the confidentiality requirements I have a masked query with anonymised data (Q2) and want to find the rank of the Q1 data point within the range of data within Q2.

I imagine I want to merge Q1 and Q2 together, but only after they've both been filtered. As an example : 

Q1 : 

Employee	Sales
Sam	10
Chase	8
Alex	6

In this query I'd filter for one employee - let's say Chase with 8 sales.

Q2 : 

Employee	Criteria	Sales
1	1	9
2	1	14
3	0	12
4	0	6
5	1	7

And in this table, we'd filter for example Criteria = 1.

We want to know what rank is Chase in the range of data of Q2 filtered for Criteria = 1.

I want to have this query : 

Q3 : 

Employee	Criteria	Sales
1	1	9
2	1	14
5	1	7
Chase	NULL	8

And show that Chase is ranked as #3 of 4 "distinct" employees in Q3.

I can figure out how to do a dynamic ranking system using a measure, but am having trouble on how to approach the merging of Q1 into Q3 AFTER both Q1 and Q2 have been filtered. Any thoughts on how to do this? 

[v-xulin-mstf · Accepted solution]

Hi @brspencer90,

Append the two tables:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MVdJRMjRQitWJVnLOSCxOBXItwDzHnNQKIMdMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Sales", Int64.Type}}),
    #"Appended Query" = Table.Combine({#"Changed Type", Q2})
in
    #"Appended Query"

Crete two slicer table:

Q1_slicer = 
VALUES(Q1[Employee])

Q2_slicer = 
VALUES(Q2[Criteria])

Create measure as:

Measure = 
IF(
    MAX(Q3[Employee]) IN ALLSELECTED(Q1_slicer[Employee])||MAX(Q3[Criteria]) IN ALLSELECTED(Q2_slicer[Criteria]),
    1,
    0
)

Here is the output:

The demo is attached, please try it.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!