Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

PowerBI Service - Scheduled Refresh

Hi    I've been trying to schedule refresh a report which works perfectly on PowerBI desktop (Ignore privacy level setting).  PowerBI service  has been throwing the following error when I refresh t...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hey so the problem didn't get solved based on the documents that were shared here but I actually reimplemented the entire report and ended up running in this permission issue fiasco at the very end but this helped me realise what the actual problem was. I will try my best to explain it here so here goes.

 

So let's say we have a table (T1) with certain details like account id, license id, license name etc. Now the issue occurs since I am calling cosmos for each row. This for each on each row calls the following query:  "select * from c where c.Type = 'doctype' and c.AccountId = '"&[AccountId]&"' "

 

Now this works flawlessly for my case when no privacy level check is done but causes problems when I try to refresh the report on power bi service

v-kkf-msft's avatar
v-kkf-msft
Community Support
5 years ago

Hi Anonymous ,

 

I tested and found that there are two ways to refresh successfully.

 

1. Manually enter the NotScannedAccounts[AccountId] column you quoted in the query, as shown below. But this will make the AccountID value fixed.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AccountId = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AccountId", type text}})
in
    #"Changed Type"

 

 

let
    final = Text.Combine(EnterTable[AccountId], "','"),
    Source = DocumentDB.Contents("https://xxxxxx", "ToDoList", null, [Query = "SELECT VALUE root from (SELECT c.id, MAX(c._ts) as maxval FROM Items c where c.id in ('"&final&"') group by c.id ) as root "]),
    #"Expanded Document" = Table.ExpandRecordColumn(Source, "Document", {"id", "maxval"}, {"Document.id", "Document.maxval"})
in
    #"Expanded Document"

 

 

2. Import all the data into PowerBI, and then use parameters or manual or DAX to filter the data.

 

 

Best Regards,
Winniz

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