Ranking measure based on combination of different dimensions

[Anonymous]

HI.

I have a ranking measure in power BI that ranks a column based on sales. This column is a combination of market and product family, I have created this column by joining the market and product family dimensions to my fact table. That way I can concatenate the market and product family columns into one column in my fact table.

However this methodology is not very good for my sql query performance so it takes longer to query as I do the joins, so I want to modify my measure so that it ranks the combination of market and product family from their dimension tables, which are of course joined to my fact table by their respective keys. This is my current measure:

Ranking Sales Market-ProdFamily =

IF(

AND( [SalesMeasure] <> 0, NOT( ISBLANK([SalesMeasure] ) ) ),
RANKX( ALLSELECTED( SalesTable[Market-ProdFamily] ) , ABS( [SalesMeasure] ) , , DESC , Skip ) , 999999999
) ..

But as I said this only works because I have Market-ProdFamily column that had already been created previously in my fact table after joining the dimensions. 

Thank you

My three tables are 

Product_ID	Product	ProductFamily
123456	A1	AAA
123457	A2	AAA
123458	A3	AAA
123459	B1	BBB
123460	B2	BBB
123461	B3	BBB
123462	C1	CCC
123463	C2	CCC
123464	C3	CCC
123465	D1	DDD

Market_ID	Market
1	US
2	ES
4	FR
5	DE
6	GR
7	PT
8	PL
9	CA
10	AT

Product_ID	Market_ID	Sales
123460	ES	619
123456	CA	861
123460	ES	965
123456	GR	555
123464	GR	351
123462	DE	718
123460	ES	357
123463	GR	617
123461	FR	444
123460	US	904
123461	ES	295
123458	GR	203
123463	ES	437
123462	ES	846

[ThxAlot · Accepted solution]

I didn't see any necessity to join tables; relationships work like a charm in most models to implicitly liaise tables. This is exactly the elegance and essence of PBI.

Simple enough,

[Anonymous]

Exactly what I needed, I was trying with a cross join but this is much better. thanks a lot!