mdawesdigitalk Tracker

Re: show items with no data as 0 instead of blanks

mdawesdigitalk — Fri, 10 Jan 2020 10:36:49 GMT

I also have a similar issue that I just can't get my head around..and the "+0" method hasn't solved it.

I have a complex model, but for this purposes have stripped it backed to simple basics, and still can't resolve it, even though I have previously I think.

In simple terms we have a customer base which is not active in every month on every product.

Therefore when comparing one month of Sales with another, you get blank entries in the database as part of the dataload relating to Customers, such as follows (removing the product variable to keep things even simplier);

1 table (DATA) with 3 Members as follows Customer, Month and Sales

Cust1, Month1, 100

Cust1, Month2 ,200

Cust1, Month3, 50

Cust2, Month1, 75

Cust2, Month3, 40

Cust3, Month2, 78

Cust3, Month3, 80

So Cust1 is active is all Months, Cust2 active in all months but Month2, and Cust3 active is all months but Month1.

My issue is that I'm trying to generate a simple Sales Variance (current month - previous month) report across all months for all Customers. My totals balance, but the analysis by Customer is missing rows where when the Customer is not active in the "Current Month" - example table below for Month 2 as Current Month

Cust1 CntMth = 200, PrvMth = 100, SalesMove = 100

Cust3 CntMth = 78, PrvMth = 0, SalesMove = 78

Totals CntMnth = 278, PrvMnth = 175, SalesMove = 103

What is missing is the Cust2 line of CntMnth = blank (and so not included), PrvMnth 75, SalesMove = -75

[Note in my model I have actual dates of "01/09/2019, 01/10/2019, 01/11/2019" respectively but have kept things generic here.]

The measures that I have used are as follows but the adding "+0" has not solved the issues as the blanks remain;

CntMnth = CALCULATE(sum(DATA[Sales]), DATEADD(DATA[Month],0,MONTH)) + 0

PrvMnth = CALCULATE(sum(DATA[Sales]), DATEADD(DATA[Month],0,MONTH)) + 0

SalesMove = CALCULATE([CntMnth] - [PrvMnth]) + 0

Any guidance, which I assume is very basic and simple, appreciated, as I've just got a total block on this, and shouldn't have...

Thanks

Mark

Re: Moving from personal gateway to the enterprise gateway

mdawesdigitalk — Wed, 04 Jan 2017 12:49:57 GMT

Hi,

Had worked that part out already.

Our main issue is that we have mixed datasets, pulling from a mix of excel files, On-Premise and Azure SQL databases, and finally Dynamics CRM in the Microsoft Cloud.

The first ones work fine, but we have had to breakout the Dynamics feeds into new datasets as they use ODATA to connect that doesn’t seem to be supported within the Enterprise gateway.

That is annoying as it was within the personal one.

Regards

Mark

Re: Enterprise Gateway and multi (cloud) data source compaitibility issues

mdawesdigitalk — Tue, 03 Jan 2017 17:12:45 GMT

I also have this same problem and finding it rather frustrating.

Re: Manage Gateways - Adding Odata Connection

mdawesdigitalk — Tue, 03 Jan 2017 17:10:59 GMT

Did this ever get solved as we have just lost the ability to use the personal gateway and therefore need to use the Enterprise gateway instead. However we now have issues on both sides meaning that neither will work for us !!

Just need to be able to have OData connection to Dynamics along with onpremises files available in a single gateway process.

Re: Moving from personal gateway to the enterprise gateway

mdawesdigitalk — Tue, 03 Jan 2017 17:14:51 GMT

And us - did this get solved ?

Our issue is that we have converted and now found that we can't mix on-premises files with Cloud data such as Dynamcis within the same dataset that updates. !!

Re: Row Level Security only works on Reports in Service and not Dashboard Tiles ?

mdawesdigitalk — Wed, 20 Jul 2016 10:46:31 GMT

HI Qiuyun_Yu,

I have retested on a very simple dataset using two users and no groups, and it now seems to work.

However, we are still seeing issues within our original "live" dataset within our group shares, so I assume it is related to a table join, or the way that the groups have been set up.

I will continue to investigate.

Mark

Row Level Security only works on Reports in Service and not Dashboard Tiles ?

mdawesdigitalk — Thu, 14 Jul 2016 08:42:09 GMT

Hi,

We have been experimenting with RLS this week as the new Role Feature in PBI Desktop makes this now workable.

However, when we publish the PBI file up to Service, and then create a dashboard, we are finding that the RLS only applies to the Report, and is not refelected within the actual dashboard tiles, as these still show all data regardless of the user that logs into the Service. If they click the dashboard tile to enter the source report then the security is applied thorughout the displyaed report ?

Is anyone else having this issue.

Any ideas on what we may have done wrong appreciated.

Thanks

Mark

Re: PowerBI refresh fail - Expression Error.

mdawesdigitalk — Fri, 01 Jul 2016 12:48:18 GMT

Same error with us as follows;

An error occurred while processing the data in the dataset.

XML parsing failed at line 83, column 40: Semicolon expected.

Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net

Time: 2016-07-01 10:05:32Z

This is a dataset that has been refreshing 2 or 3 times a day for 6 months without fail, until yesterday.

If I take the original desktop file and refresh I get no errors. Reupload that refreshed file onto Service via OneDrive and the data refreshes, but the dataset will not refresh within Service.

Any pointers appreciated, but suspect it is a Microsoft introduced bug somewhere.....

Re: PowerBI refresh fail - Expression Error.

mdawesdigitalk — Fri, 01 Jul 2016 12:40:43 GMT

Same error with us as follows;

Power BI

This is a dataset that has been refreshing 2 or 3 times a day for 6 months without fail, until yesterday.

If I take the original desktop file and refresh I get no errors. Reupload that refreshed file onto Service via OneDrive and the data refreshes, but the dataset will not refresh within Service.

Any pointers appreciated, but suspect it is a Microsoft introduced bug somewhere.....

Re: Power BI Group and File (OneDrive for Busines) Integration

mdawesdigitalk — Mon, 07 Dec 2015 14:36:25 GMT

Please note that Microsoft are now showing that Incident PB34667 that relates to the above was resolved on Friday 4th December.

However we are still seeing the error, but it seems that Microsoft have stopped working on this.

Any other ideas on how to avoid these issues appreciated.

Regards

Mark