CraigSchulz Tracker

Re: API calls with Pagination

CraigSchulz — Thu, 22 Nov 2018 12:52:52 GMT

Okay, thanks for the input.

API calls with Pagination

CraigSchulz — Wed, 21 Nov 2018 01:30:37 GMT

Using the code below I am able to access data via an API and pull the first 100 records. My problem and my question is, how can I get this code to iterate through a series of offsets so that I can pull 2000 records instead of just 100?

let
Source = Json.Document(Web.Contents("https://api.recruiterbox.com/v2/candidates?limit=100&offset=0")),
objects = Source[objects],
expandrecords = Table.FromRecords(objects),

Re: Expand Column Containing list of Records from API Call

CraigSchulz — Sun, 18 Nov 2018 15:45:16 GMT

Thanks! That worked great, and so much simpler than what I had been trying.

Re: Expand Column Containing list of Records from API Call

CraigSchulz — Sat, 17 Nov 2018 21:39:40 GMT

Thanks, that seems right. I'm trying it, but what is the argument that I should pass to Table.FromRecords(<records as list>)? The Syntax help in Microsoft Docs isn't very helpful.

Expand Column Containing list of Records from API Call

CraigSchulz — Sat, 17 Nov 2018 19:07:11 GMT

First, I know there are similar threads, but I did not see any just like this. Following an API call I have this:

What command or function should I use in order to return a table where the records have been expanded to include all the fields in a record? If I click on list, I get the following:

I attenpted to us Table.ExpandRecordColumn but ended up with an error along the line of "Type Error - could not convert type list to type table.

What should I use?

Re: Publishing to a Client's PBI Service

CraigSchulz — Thu, 08 Nov 2018 22:34:09 GMT

Thanks. I tried this with another client, and I could only be added as a guest because my email indicates I am not part of their firm. Is that as it should be? Can you still publish to the Service as a guest? What credentials will be asked for? I'm still confused, sorry.

Publishing to a Client's PBI Service

CraigSchulz — Wed, 07 Nov 2018 20:23:54 GMT

I have a client for whom I create visualizations in Desktop and then publish to their PBI service. To do this the client has given me his own username and password in order to publish the material.

I now have another client that wants me to develop PBI material for them, but they do not want to give me their credentials, and I certainly can understand their unwillingness to do that. The question is, what is the best practice for me to get credentials to sign in and publish to the client's PBI service?

- Craig Schulz

Re: change filter options when using drill down

CraigSchulz — Fri, 28 Sep 2018 16:35:44 GMT

Here is part of a visual showing account 130, average parked at noon, this day of the week, parked at noon today, and comparison percentage today/average.Notice cars parked are 178 and 207; comparison percentage is 116%. Drilling down to parking areas within the account shows the following:

Now, my client only wants to view, at the account level, only those accounts that are either below 80% or over 120% of average. When I apply that filter at the account level I get the following:

I want account 130 not to show now as the comparison percent was 116%, but it does appear with different totals for cars parked and a comparison percentage of 122%. This is because the "less than 80% and greater than 120%" filter got applied to the area level as well as the account level, so some of the areas don't appear now because of the comparison %, the totals drop to numbers that don't represent the entire account.

In other words, I want the filter to apply at the account level, but when I drill down to the account area level, I want all areas to appear regardless of the comparison percentage. How can I make this happen?

- Craig

change filter options when using drill down

CraigSchulz — Wed, 26 Sep 2018 21:15:08 GMT

I would like to be able to change or remove visual filter options as I move down a hierarchy using drill down. I have a combined line and clustered column chart that has accounts and account areas (hierarchy) sharing the x axis and shows cars parked today, average cars parked this day of the week, and the line is a comparison percentage for parked today over average. For the top level, accounts, I only want to show those accounts where the percentage is below 80% or over 120%, but when I drill down to areas within an account I no longer want that filter active. I'm sure this must be possible, but I haven't seen or figured out how to do it. Help please.

- Craig

Re: Get Data "From Folder" from Sharepoint Folder

CraigSchulz — Fri, 14 Sep 2018 20:04:39 GMT

Thanks for the quick response. I had just used https://xxx.sharepoint.com/. Did not put in the "/sites/". And what does the final "xxx" stand arepresent?

When I do as described above I get a list of files similar to what you indicate in your response - a list of many different files and file types. The thing is, my purpose in connecting to a folder is to have a PBI query pull all the files in the folder into one table in PBI, as discussed by Rob Collie and Avichal Singh in their book Power Pivot and Power BI, 2nd Edition. It seems, therefore, that I need to have a folder populated with files all of the same type and construction, readable by PBI, xlsx of csv files for insance. Then it appears I need to have PBI point to that folder, not the root sharepoint directory.

Does the "xxx" at the end of your format represent the folder name? Sorry if I am being dense. The book does say it can be done using a Sharepoint folder IF YOU HAVE IT MAPPED AS A DRIVE. But that seems to defeat the purpose of having a PBI project that is portable and my client can continue to use if I am no longer in the picture with them.

- Craig

Get Data "From Folder" from Sharepoint Folder

CraigSchulz — Fri, 14 Sep 2018 18:19:12 GMT

Has anyone had any luck getting data using the from "Sharepoint Folder" option? When I try to point to the folder it tells me I need to put the URL for the Sharepoint root in the box. When I point to the Sharepoint root it gives me no way to get to the desired folder so that only the files of the same type in the folder that I want to read in show up. Very frustrating!

- Craig

Re: Measure Needs Different Denominator for One Account

CraigSchulz — Tue, 28 Aug 2018 13:35:41 GMT

Hey Maggie,

I'm not sure what to do following your response. I tried your recommended solution and it returned an error. Adding VALUES to the code seemed to work but the visual returned an error.

- Craig

Create New Table of Budgeted Hours with Dates Attached

CraigSchulz — Sat, 25 Aug 2018 02:31:05 GMT

I have a table (part shown below) that provides budgeted labor hours by account by day of week. Budgeted hours for a given day of week for a given account will not vary by actual date. However, I want to create a visual that shows, for any one account determined by a slicer, 28 consecutive dates on the X-axis, Monday - Sunday for 4 weeks, and shows actual labor hours worked on those dates for the selected account compared to the budgeted hours for that account and day of week. Can anyone give me an idea on how to do this? As a clarification, the budgeted labor hours portion of the visual would repeat the same set of 7 values (one for each day of the week) 4 times, a four week analysis.

- Craig

Re: Measure Needs Different Denominator for One Account

CraigSchulz — Fri, 24 Aug 2018 15:53:43 GMT

This measure did work, once I changed the first lF to IF(VALUES(AccountAreaMaster[AcctAreaID]) = "130 - Hope Tower",

It broke my visual however, giving me the following error message:

Error Message:

MdxScript(Model) (7, 5) Calculation error in measure 'SMSYTD'[AvgParkedByNoon]: A table of multiple values was supplied where a single value was expected.

Perhaps this is because the visual starts out comparing Accounts ("average parked by noon" with "parked by noon today"), and then you can drill down to show AccountAreas with the same comparison. AcctArea "130 - Hope Tower" is one of two areas under AccountID "130", the other being AcctArea "130 - Main".

Is this the source of the error?

If not, what is?

Any thoughts on a solution?

- Craig

Measure Needs Different Denominator for One Account

CraigSchulz — Thu, 23 Aug 2018 21:31:14 GMT

I have the following measure that calculates the average number of cars parked by noon on the same day of the week as the current day of the week. On a Friday, for any given account it will give me the average number for Fridays since the beginning of the year.

AvgParkedByNoon = CALCULATE(
DIVIDE(SUM(SMSYTD[ParkedByNoon]),DISTINCTCOUNT('Calendar'[Date])),
'Calendar'[IsHoliday]=FALSE(),
FILTER('Calendar','Calendar'[Day of Week] = [DoWtoday]))

This is working well, but one of the accounts is fairly new and still growing, so it doesn't make sense to look all the way to the beginning of the year for the denominator because there was no data early in the year. In fact I would like to only use the relevant days in July of this year for this particular account.

Can anyone help me with the code to do this?

Here is a link to a sample data file.

https://drive.google.com/file/d/1M2HOFq43UB7qduMqZw_EcBnQK_wecrN-/view?usp=sharing

- Craig

Full Visual Doesn't Show in Phone View

CraigSchulz — Wed, 22 Aug 2018 22:28:53 GMT

I created a visual for a client that had biweekly data points from beginning of the year to current. If it matters, it is an area chart. Uploaded it to my clients PBI service and pinned it to a dashboard. When the client views it on his phone it does not have the full data range, it only goes to mid June, although if he taps the tile it takes him to the full, up-to-date report. Is this typical? Is there a way to get the complete data range to show on the phone view?

- Craig

Re: Calculated Measure Needs Different Denominator for One of 24 Accounts

CraigSchulz — Mon, 20 Aug 2018 14:37:15 GMT

https://drive.google.com/file/d/1M2HOFq43UB7qduMqZw_EcBnQK_wecrN-/view?usp=sharing

Above is a link to a sample data file. In Power BI this file is linked to my calendar table. File shows several accounts and all the cars parked at those accounts by date, and whether the cars were parked by noon. I have another file that shows cars parked by noon today. I want a visual that compares, by account, cars parked by noon today to average cars parked by noon YTD for the same day of the week. One of the accounts, "New Tower", is new, so average cars parked YTD would be bogus since it didn't start operating until June. For that particular account I would like the denominator of the average calculation to use only dates for the month of July 2018, not the full year. For the rest of the accounts dates for the full year are fine. How can I accomplish this?

- Craig

Re: Calculated Measure Needs Different Denominator for One of 24 Accounts

CraigSchulz — Tue, 14 Aug 2018 19:58:14 GMT

I'm afraid I don't feel comfortable sharing the dataset and this is for a client project and I am not sure they would authorize sharing information about the company on a public forum. Thanks for offering your help, but I guess I'll have to figure it out on my own.

- Craig

Re: Calculated Measure Needs Different Denominator for One of 24 Accounts

CraigSchulz — Sat, 11 Aug 2018 19:45:59 GMT

Not sure how to share a dataset on this platform.

Calculated Measure Needs Different Denominator for One of 24 Accounts

CraigSchulz — Sat, 11 Aug 2018 02:55:57 GMT

AvgParkedByNoon = CALCULATE(
DIVIDE(SUM(SMSYTD[ParkedByNoon]),DISTINCTCOUNT('Calendar'[Date])),
'Calendar'[IsHoliday]=FALSE(),
FILTER('Calendar','Calendar'[Day of Week] = [DoWtoday]))

Can anyone help me with the code to do this?

- Craig