Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Alternative of a looping command in Power BI?

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

1 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

 

loooping.png


Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
v-shex-msft
Community Support
Community Support

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] )

5.PNG

 

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] ) )

6.PNG

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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



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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

loooping.png


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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. Smiley Happy

Best,
Hendrik

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

Regards
Zubair

Please try my custom visuals

@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

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.