Date filter problem.

[bob57]

Good day,

I am unable to get the date table to interact with the data table.

Here is a link to the .pbix: https://techservicesllc-my.sharepoint.com/:u:/p/bstorie/Ed6I54AMjxdNiEWDPBZXBgEByyey3TzZZHOYu73PscC9...

Notice that dates do not appear in the Date column in the below table visualization. Also, if the start date is changed to a date other than 6/1, all data in the InvTotal and Full Name columns disappear (the full date range is 6/1 - 7/6). I'm sure I lack a fundamental understanding that has prevented me from successfully debuging. I hope you're willing to help.

Thank you for your time and effort.

Bob

[gpiero · Accepted solution]

@bob57 

I would rather prefer modeling data in Query Editor but SQL connection require credential.

I tried a fast solution just to show you the result. Please check if it is ok for your needs

Regards

https://1drv.ms/u/s!Ah7_1Sua__g-6zhVFT68Zy4_tXmQ?e=yWwFOX 

[gpiero]

Hi @bob57 

Can youl help me to clarify which Date you wanto to display?

I suppose there is a issue in the join among table.

If you select CtrmID = "ALF" you will find 2004 rows and several InvDateTime for those rows.

Which date you would like to display? the earliest? the latest?

I can't edit your model to help you since credential are required

Regards

[bob57]

My apologies. Try this link as I don't believe that credentials will be required for this one: https://1drv.ms/u/s!Agkx6hQs0NiX1A2HsUc2gNJ5snqS?e=fB5U2R.

Ultimately, there will be several filters but, for now, I need to see the invoice totals for each day/per person. I agree that there is likely a problem with the join, but I have been unable to isolate it. Everything looks good to me.

Happy to provide more clarity if necessary.

Thank you!

[gpiero · Accepted solution]

@bob57 

I would rather prefer modeling data in Query Editor but SQL connection require credential.

I tried a fast solution just to show you the result. Please check if it is ok for your needs

Regards

https://1drv.ms/u/s!Ah7_1Sua__g-6zhVFT68Zy4_tXmQ?e=yWwFOX 

[bob57]

That is a solution thank you! I don't understand why I couldn't join with InvDateTime. As I indicated early on, I need to improve on the fundamentals. If you can offer a brief explanation, I would be most appreciative. Otherwise, I will continue to study and improve.

I'm grateful for your time and effort.

Bob

[bob57]

Sorry, I jumped the gun. I didn't relize that you used MyInvDate for the Date column. So, if you alter the date range using the date filter, you notice that the date filter has either no impact (if you change the last date) or n adverse impact (if you change the start date). Again, my apologies, if my line of questioning has been confusing.

Bob

[gpiero]

Change the field in the visual. Substitute Date with MyInvDate

[gpiero]

@bob57 

ok let me see if I can provide a different solution. I'll try next Monday.

[gpiero · Accepted solution]

@bob57 

What about this solution?

I did a workaround to overcome the credential issue. I created a flat files in DASX Studio using yout pbix and then I loaded in a new one.

Let me know

https://1drv.ms/u/s!Ah7_1Sua__g-6zkpOXZHDHM_iLmU?e=Sxx3ua 

[bob57]

Perfect! So it looks like I had to use query editor to split the date/time column and change the types to Date and Time respectively.  Then join the Dates table on the new InvDate column of the vSalesHeader table.

Thank you for putting in a little extra time on my behalf.

Bob

[gpiero]

@Bob 

Hi,

just some little improvement

https://1drv.ms/u/s!Ah7_1Sua__g-6zkpOXZHDHM_iLmU?e=G5rivv 

Regards

P.S.: please check at your PM inbox

[gpiero]

I am not completely sure, but an explanation could be an issue related to data type.

For this reason I tried to apply the split option using Transform  in Query Editor.   I wanted to divide datetime field ito only date.

But I had not the access due credential.

I did a lot of PBI report from Sharepoint workflow list since 2016 and I always adopted this strategy. I rather prefer to have the complete control of the data even if it could mean to adopt a solution considered not up to date.

I always put in join a field Date from Calendar table with field Date from Share point list.

You know, we use to say that "Experience is the result (or the sum) of the kick off in your bottom)😎.
Regards