Rank x

[JamesBurke]

Hi , 

Solved: Count Top 1 Rank per day - Microsoft Fabric Community

I'm intrested in doing something similar to this but for Months however my datasets are split into tables and do not have the "Sum of Points" as well as Team in one. 

I have multiple tables one being with Devices , one with device usages and one with the date. 

The device tables are connected with Dim device ID and The Date with Dim date id , i'm wondering if it's still possible to potentially create another table with the Month , Device and usages all in one table so i can create a rank conditonal column and then count how many times that store was rank one. 

Thanks , James. 

[v-zhengdxu-msft · Accepted solution]

Hi @JamesBurke 

If you want to create another table with data from these 3 tables, CROSSJOIN could be a good choice.

Here I create a set of sample for your reference:

Then add a table:

Table = 
	FILTER(
		CROSSJOIN(
			SELECTCOLUMNS(
				'Device',
				'Device'[Values2],
				"DeviceID", 'Device'[ Dim device ID],
				"DateID", 'Device'[Dim date id]
			),
			'DeviceUsage',
			'Date'
		),
		[DeviceID] = 'DeviceUsage'[ Dim device ID] && [DateID] = 'Date'[Dim date id]
	)

The result is as follow:

Best Regards

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