Help to reference columns from a var table to another var table!

[acolin]

Hello, I really need help with this one. The problem is that I have a table with Users IDs (UIDs) and Devices IDs (DIDs), and I want to get through a MEASURE the values of how many DISTINCT COUNT of UIDs are related to the quantity of DISTINCTS DEVICES.

UID	DID	Dist_DID	Num_Dist_UID
U1	D3	2	3
U1	D54	2	3
U2	D8	1	2
U3	D9	2	3
U3	D11	2	3
U3	D11	2	3
U4	D23	1	2
U5	D35	2	3
U5	D87	2	3

This is the "original" table:

User ID	Device ID
U1	D3
U1	D54
U2	D8
U3	D9
U3	D11
U3	D11
U4	D23
U5	D35
U5	D87

My idea was to first, create a TBL to extract the distinct count of the DIDs for each user:
VAR temp = SUMMARIZE(TBL, TBL[User ID], "Distdisps", DISTINCTCOUNT(TBL[Device ID]))

User ID	Distdisps
U1	2
U1	2
U2	1
U3	2
U3	2
U3	2
U4	1
U5	2
U5	2

Then aided by a second table, that contains the distinct values of numbers of IDs,...

VAR temp2 = DISTINCT(SELECTCOLUMNS(temp, "Distdisps", [Distdisps]))

Distdisps

1

2

3

5

...

n

...create a third table to calculate the distinct count of UIDs via filtering the UIDs of the first table by the different numbers of DIDs and then applying the distinct count to count how many UIDs had the same numbers of different DIDs. Meaning that this table will contain only unique Numbers of Distinct Devices.
VAR temp3 = SUMMARIZE(temp2, temp2[Distdisps], "Distusrs", CALCULATE(DISTINCTCOUNT(temp[User ID]),
FILTER(temp, temp[Distdisps] = temp2[Distdisps])))

Distdisps	Distusrs
1	3
2	2
3	7
5	1
...
n	m

Finally, create the variable that will show in a visual the Number of Distinct UIDs that are related to 1, 2, ..., n different devices.
So, in the category field of the visual, I added a table (Numbers) that is filled with one column (IDX) listed from  1 to 500, hoping to relate these cells with the third table in the measure to filter the results and display just the corresponding value. The SUM function is just to force a scalar value if, for example, multiple IDX values are selected.

VAR calculo = CALCULATE(SUM(temp3[Distintos usrs]), FILTER(temp3, temp3[Distintos disps] = SELECTEDVALUE(Numbers[IDX])))

RETURN
calculo

The problem is, I can't access these columns through the pseudocode that I just share with you guys, that's why I'm asking for help, I'm a newbie. I'm really sorry to bother you, but if someone can help me out I'll be very grateful. Also, sorry for my English, it's not my native language. Hope to be very clear with my explanation.

Pseudocode once again:
VAR temp = SUMMARIZE(TBL, TBL[User ID], "Distdisps", DISTINCTCOUNT(TBL[Device ID]))
VAR temp2 = DISTINCT(SELECTCOLUMNS(temp, "Distdisps", [Distdisps]))
VAR temp3 = SUMMARIZE(temp2, temp2[Distdisps], "Distusrs", CALCULATE(DISTINCTCOUNT(temp[User ID]),
FILTER(temp, temp[Distdisps] = temp2[Distdisps])))

VAR calculo = CALCULATE(SUM(temp3[Distusrs]), FILTER(temp3, temp3[Distdisps] = SELECTEDVALUE(Numbers[IDX])))

RETURN

calculo

[acolin]

Thanks for your kindness and answer! Not precisely what I wanted to do, so I changed the code a little bit and it worked for me.

var _t0 = SUMMARIZE(tblfraudesantander, tblfraudesantander[User ID], "Distdisps", CALCULATE(DISTINCTCOUNT('tblfraudesantander'[Device ID]), 'tblfraudesantander'[User ID]=EARLIER([User ID]), ALL('tblfraudesantander'[Device ID])))
var _t1 = DISTINCT(SELECTCOLUMNS(_t0, "Distdisps", [Distdisps]))
var _t2 = ADDCOLUMNS( _t1, "Distusrs", 
                var _Distdisps = [Distdisps]
                var _test = DISTINCT(SELECTCOLUMNS(FILTER(_t0, [Distdisps]=_Distdisps), "UID", [User ID]))
                return COUNTROWS(_test))
var _t3 = FILTER(_t2 , [Distdisps] in VALUES(Indice[IDX]))
RETURN
SUMX(_t3 , [Distusrs])

The _t2 stage was the key part of my trouble, didn't know how create that filter for the _t0 table. Also what a nice profile pic there, nice anime series!

[v-yueyunzh-msft · Accepted solution]

Hi , @acolin 

Thanks for your sample data and detailed description.

According to your description, in my thought , you want to create a measure to get the sum of [Distusrs] , like this:

If this , you can refer to :
(1)We can crecate a number as row headers:

We do not need to create any relationship between tables.

(2)You can create a measure like this:

Measure = var _t =  ADDCOLUMNS('Table',"Distdisps" , CALCULATE(DISTINCTCOUNT('Table'[DID]) , 'Table'[UID]=EARLIER([UID]) ,ALL('Table'[DID])))
var _t2 = ADDCOLUMNS( _t ,"Distusrs", var _Distdisps = [Distdisps] var _test = DISTINCT(SELECTCOLUMNS( FILTER(_t ,[Distdisps]=_Distdisps),"uid" ,[UID]))  return COUNTROWS(_test)     )
var _cur_num =  VALUES('Number'[Column1])
var _t3 = FILTER(_t2 , [Distdisps] in _cur_num)
return
SUMX(_t3 , [Distusrs])

Then we can put this measure on  the visual like this , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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