How to perform Cross Join between two columns with little bit different approach

[codingenthu]

I have 2 columns from two different tables as below:

Table1:

size

1

2

3

4

Table2:

unit

50

49

48

47

Desired Output:(New Calculated Table)

size	unit
1	50
2	50
2	49
3	50
3	49
3	48
4	50
4	49
4	48
4	47

So based on size value(number) from the Table1,  the top values should return in new rows for example: if size value is 3 then top 3 values, if it is 4 then top 4 values and so on..

Can somebody please help on how can i achieve this in DAX?

[vicky_ · Accepted solution]

Here's my attempt:

New Table = 
var crossjoined = CROSSJOIN(unit, size)
var toReturn = FILTER(crossjoined, [size] >= RANK(unit, ORDERBY([unit], DESC)))
return toReturn