Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
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
Solved! Go to 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
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
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] ) )
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
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
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
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
@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
User | Count |
---|---|
92 | |
88 | |
88 | |
79 | |
49 |
User | Count |
---|---|
156 | |
145 | |
105 | |
72 | |
55 |