Forum Discussion

acolin's avatar
acolin
Frequent Visitor
3 years ago
Solved

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

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.
UIDDIDDist_DIDNum_Dist_UID
U1D323
U1D5423
U2D812
U3D923
U3D1123
U3D1123
U4D2312
U5D3523
U5D8723

 

This is the "original" table:

User IDDevice ID
U1D3
U1D54
U2D8
U3D9
U3D11
U3D11
U4D23
U5D35
U5D87

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 IDDistdisps
U12
U12
U21
U32
U32
U32
U41
U52
U52

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])))
DistdispsDistusrs
13
22
37
51
... 
nm

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

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

2 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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

     

  • acolin's avatar
    acolin
    Frequent Visitor
    3 years ago

    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!