Alternative of a looping command in Power BI?

[Anonymous]

Hi All,

I'm fairly new in DAX or M query and wondering if there's any alternative way of doing a looping command inside power BI. I have a dataset of Referee X Advocate X Merchant and wanted to count the referral degree of any particular referee in the dataset. Below is a sample of the dataset.

RefereeId  | AdvocateID | Merchant | Expected Referral Degree
      1         |         2          |        A       |                  3
      2         |         3          |        A       |                  2
      3         |         4          |        A       |                  1

For the last row, the expected result is 1 because the advocateID '4' hasn't been advocate by anyone with the same merchant. While for the second row, the result is 2 because the AdvocateID '3' has been once been a referee by advocateID '4' (last row). Last, the first row will result as 3rd degree since the AdvocateID '2' was referred by AdvocateID '3' in the second row and AdvocateID '3' itself is a 2nd degree hence AdvocateID '2' is 2+1 or 3rd degree.

What i meant with looping is when i check the first row, i need to check the AdvocateID whether or not he/she has been referred before, and it keep looping until we found no data of referral for the last AdvocateID.

I hope i explain this clearly enough and if it helps, there are timestamp column on the table as well where the row above is sorted descendingly (from most recent to latest data) regards to the timestamp.

Is there any way to do a workaround for this? Any help will be much appreciated!

Best,
Hendrik

[v-shex-msft]

Hi @Anonymous,

It seems like your table is a parent child relationship based on two column, I'd like to suggest your path functions to deal with this scenario.

Notice: Path function require parent field has correspond child field value, you need to replace these unmatched 'Advocate ID' as blank before calculation.

Calculate column formula:

Replaced Advocate =
IF ( [AdvocateID] IN ALL ( Table[ID] ), [AdvocateID], BLANK () )

Expected Referral Degree =
PATH ( [ID], [Replaced Advocate] )

For your requirement, I think result who has more complex reference tree should had better priority, so I use 'pathlength' function to return count of path length as priority number.

Modified formula:

Expected Referral Degree =
PATHLENGTH ( PATH ( [ID], [Replaced Advocate] ) )

In condition, you can also try to write a custom function to calculate these in power query, but power query has poor performance to progress large amount of records.(over hundreds records)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft,

Thanks a lot for pointing out to PATH functions. I believe it is the best function to use. However, when trying to my dataset, i encounter some error. I've tried a few things out and still stuck at this error:

"Each value in 'Reward Referral Users'[GCIUserId] must have the same value in 'Reward Referral Users'[Replaced AdvocateId]. The value '0' has multiple values."

To reproduce this error, let me update the table i above to replicate my real table better. I add another 4 rows to represent referee and advocate from other merchants. 

RefereeId  | AdvocateID | Merchant | Expected Referral Degree
      1         |         2          |        A       |                  3
      2         |         3          |        A       |                  2
      3         |         4          |        A       |                  1
      1         |         2          |        B       |                  4
      2         |         3          |        B       |                  3
      3         |         4          |        B       |                  2
      4         |         5          |        B       |                  1

I've followed your step and customize to my need by adding merchant in the filter when calculating the replaced advocate id.

Here's what I write for my ReplaceAdvocateId formula:

VAR refereeTable =

CALCULATETABLE(
    DISTINCT(Table[RefereeId],
    ALLEXCEPT('Table','Table'[Merchant])
)

RETURN
IF(
    Table[RefereeId] in refereeTable,
    Table[AdvocateId],
    BLANK()
)

And the above formula resulted to: (which i think is correct already)     

RefereeId  | AdvocateID | Merchant | ReplacedAdvocateId
      1         |         2          |        A       |                  2
      2         |         3          |        A       |                  3
      3         |         4          |        A       |                  
      1         |         2          |        B       |                  2
      2         |         3          |        B       |                  3
      3         |         4          |        B       |                  4
      4         |         5          |        B       |                  

However, when try to use the PATH function:

CALCULATE(
    PATH('Table'[RefereeId],'Table'[ReplacedAdvocateId]),
    ALLEXCEPT(
        'Table',
        'Table'[Merchant]
    )
)

It returns above error. I already remove all duplicate so that each refereeId for each merchant only have one advocate id but still stuck at this error. 

I would really appreciate if you can help me out of this error.

Best,
Hendrik

[v-shex-msft]

Hi @Anonymous,

I test to fix this on my side but failed, it seems like path function not support to customize on it and not effect by filters.

Regards,

Xiaoxin Sheng

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

As @v-shex-msft pointed out we can do it in Power Query as well using Recursive function but the performance might be bad. But give it a shot.It works with your last sample data.

Please see attached file's Query Editor

The code in red is the custom function

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYkcgNlaK1YkG84yhIkZgERDPBCpiCBaB6XICyyDrcoKbA9PlBDcHxDOFigDNiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RefereeId = _t, AdvocateID = _t, Merchant = _t, #"Expected Referral Degree" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"RefereeId", type text}, {"AdvocateID", type text}, {"Merchant", type text}, {"Expected Referral Degree", Int64.Type}}),
     myfunction=(myvalue,mymerchant)=>
                    let
                    mylist=Table.SelectRows(ChangedType,each [RefereeId]=myvalue and [Merchant]=mymerchant)[AdvocateID],
                    Merch=Text.Combine(Table.SelectRows(ChangedType,each [RefereeId]=myvalue and [Merchant]=mymerchant)[Merchant]),
                    mycount=List.Count(mylist),
                    result=Text.Combine(mylist)
                    in
                    if mycount=0 then 1   else  mycount + @ myfunction(result,Merch),

Final= Table.AddColumn(ChangedType, "Custom Column", each myfunction([AdvocateID],[Merchant]))                
in
    Final

The result

[Anonymous]

Hi @Zubair_Muhammad,

Really appreciate the help! I tried it out both on my real dataset and my dummy (smaller) dataset. It works perfectly for my smaller dataset 10+ rows but it seems to be very slow on my real dataset (10k++ row). I have left it running for 30 minutes now and only 100+ rows are loaded.

I'm wondering will it be faster if I use python script instead. Any experience on that in terms of the processing speed?

Still waiting for @v-shex-msft on the guidance for the DAX help. 

Best,
Hendrik

[Zubair_Muhammad]

HI @Anonymous

Please see if this revision speeds up the query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYkcgNlaK1YkG84yhIkZgERDPBCpiCBaB6XICyyDrcoKbA9PlBDcHxDOFigDNiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RefereeId = _t, AdvocateID = _t, Merchant = _t, #"Expected Referral Degree" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"RefereeId", type text}, {"AdvocateID", type text}, {"Merchant", type text}, {"Expected Referral Degree", Int64.Type}}),
    ChangedType1=Table.Buffer(ChangedType),
     myfunction=(myvalue,mymerchant)=>
                    let
                    mylist=List.Buffer(Table.SelectRows(ChangedType1,each [RefereeId]=myvalue and [Merchant]=mymerchant)[AdvocateID]),
                    mycount=List.Count(mylist),
                    result=Text.Combine(mylist)
                    in
                    if mycount=0 then 1   else  1 + @ myfunction(result,mymerchant),

Final= Table.AddColumn(ChangedType, "Custom Column", each myfunction([AdvocateID],[Merchant]))                
in
    Final

[Zubair_Muhammad]

@Anonymous

This is another method (using List.PositionOf)

Please try this as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYkcgNlaK1YkG84yhIkZgERDPBCpiCBaB6XICyyDrcoKbA9PlBDcHxDOFigDNiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RefereeId = _t, AdvocateID = _t, Merchant = _t, #"Expected Referral Degree" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"RefereeId", type text}, {"AdvocateID", type text}, {"Merchant", type text}, {"Expected Referral Degree", Int64.Type}}),
    ChangedType1=Table.Buffer(ChangedType),
     myfunction=(myvalue,mymerchant)=>
                    let
                    mytable=Table.SelectRows(ChangedType1,each [Merchant]=mymerchant),
                    mylist=List.Buffer(mytable[AdvocateID]),
                    myotherlist=List.Buffer(mytable[RefereeId]),
                    Pos=List.PositionOf(myotherlist,myvalue),
                    NextValue=mylist{Pos}
                    in
                    if Pos=-1 then 1   else  1 + @ myfunction(NextValue,mymerchant),

Final= Table.AddColumn(ChangedType, "Custom Column", each myfunction([AdvocateID],[Merchant]))                
in
    Final