Calendar does not work

Used for anything related to the new Power BI Desktop and Service tools
Bilo
Posts: 26
Joined: Fri Nov 16, 2018 7:37 am

Calendar does not work

Postby Bilo » Tue Mar 05, 2019 5:12 am

Hi,

I have 2 matrices in Power BI desktop (see pdf attached)
On the left of the image, a matrix with the number of scores per year from "Result" table
On the right the same matrix with the score per year from "Calendar" table
The matrix on the left works correctly but not the one on the right
Do you have any idea please ?

Thank you in advance for your help
Attachments
CalendarYear.pdf
(891.45 KiB) Downloaded 80 times

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Calendar does not work

Postby PhilC » Tue Mar 05, 2019 8:02 pm

Hi Bilo,

Thanks for posting the visuals of what you have. Providing a PowerBI file would also help as we can edit and show directly in the file how to approach things.

I am interested in how you have the model as I would not have thought it would allow the connection between the Result and Calendar tables, as I am assuming the Year field is just in the format of YYYY. The link from Result[Year] to Calendar[Year] would usually have an error message saying cannot link many to many.

Irrespective of this, it is the reason the matrix shows up incorrectly when using the Calendar[Year] field.

If your data is yearly and not daily, you need to use an actual date. I generally use 31/12/YYYY. You can do this transformation under Edit Queries. Try Column from Example (use Google to see how to use it). Simply click on Result[Year] column, then Add Colum and use Column from Example. Type in 31/12/2016 (or whatever the year is in the first row), one of the option should say something like Last day of Year. Click this and then Ok to use that as the transformation for the new column. Name the column something useful (Date), and change to date format if necessary.

You can then link this new Date field to the Calendar table on the Calendar[Date] field.

This should not alloy you to use the Year column from Calendar table.

You should also consider writing the measure for counting the # scores rather than letting PBI do it implicitly (google Implict vs Explicit measures in DAX).

Matt's book on Supercharge BI is a great reference and walks you through much of this. (I did Matt's initial Power BI course and have used two of his books, they are great references to get things right as you start on your PBI journey)

Cheers
Phil

Bilo
Posts: 26
Joined: Fri Nov 16, 2018 7:37 am

Re: Calendar does not work

Postby Bilo » Wed Mar 06, 2019 7:46 am

Hi Phil,

Thank you for your answer and advice,
You are right that a file is preferable to images to better understand the model and please excuse me.
I attach a small example file with Power BI desktop. Strangely enough, the calendar works this time with this model...!!!
But I am sure that this model is not optimal and that you will find mistakes or that you will have good advice to give me.
And with the content of the tables, you will quickly understand the kind of statistics I'd like to get.

Otherwise, with regard to the books you suggest to me, I unfortunately do not have enough English to understand. If you have training videos, even for a fee, I would be interested.
Thank you in advance for your help
Have a good evening
Attachments
TestBilo.pbix
(87.4 KiB) Downloaded 66 times

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Calendar does not work

Postby PhilC » Wed Mar 06, 2019 12:08 pm

Thanks for providing the PBIX file, definitely an interesting dataset to be analysing.

To do anything meaningful with this, you will need to start using measures, not just dragging a field into the values area of a visual. I mentioned below to google the difference between implicit and explicit measure in Power BI, this will give you a good start.

I would expect there would be a book available in your native language on Power BI, try to find one as spending a few dollars (most are under $50) will save you much more in productivity and capability, invest in yourself. Otherwise find a Power BI blog to help, there are many in English, hopefully some in your language.

If you speak French these forums might be of use:
https://community.powerbi.com/t5/Data-Stories-Gallery/The-French-Power-BI-Community/td-p/198495
http://clubpowerbi.com/

There also appears to be a couple of Power BI related books on Amazon in French.


Cheers
Phil

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Calendar does not work

Postby PhilC » Wed Mar 06, 2019 12:09 pm

Power BI file attached, with a simple COUNTROWS measure added.

Cheers
Phil
Attachments
TestBilo PPM.pbix
(90.28 KiB) Downloaded 52 times

Bilo
Posts: 26
Joined: Fri Nov 16, 2018 7:37 am

Re: Calendar does not work

Postby Bilo » Thu Mar 07, 2019 6:40 am

Hi Phil,
Thank you very much for your answer and the links on the forums in French,
Regarding my knowledge of "Power BI or Power Pivot, Power query, Power View" in Excel, I have an account on LinkedIn Learning where I have viewed several dozen hours of training on this subject

I also partially read 2 books in French on "Power query and the M code" as well as "Dax for Power Pivot" that I bought a few months ago.
I currently have a good foundation but not enough practice to master the Dax language which is very powerful
Through my apprenticeship, I also learned how to build a powerful data model and how to link tables together.
I tried to buy training videos on the sqlbi.com website (Alberto Ferrari) but I didn't really understand how this site works: obviously only by subscription but I'm looking to buy these videos and at a reasonable price

Concerning my Power BI example file, the measure remains very simple and more appropriate. However, how the measure would be written for calculate the number of matches with more than 1.5 goals knowing that I do not have this information in the fact table but only in the dimension table ?
Would you have presented this data model differently ?

P.S: Is your site in Australia ?
Otherwise I was pleasantly surprised that all the posts on this site are automatically translated into good French on the mobile phone only.
I don't know which translation site is used for this but it is very good

Thank you again for your help
Have a good evening

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Calendar does not work

Postby PhilC » Thu Mar 07, 2019 7:06 am


Bilo
Posts: 26
Joined: Fri Nov 16, 2018 7:37 am

Re: Calendar does not work

Postby Bilo » Fri Mar 08, 2019 6:00 am

Hi Phil,

Thank you for the link to the best practices of the power Bi Tools that I would read carefully.

I had already viewed a similar tips in Chris Dutton's excellent training "Excel Business Intelligence Part 2: Data Modeling 101" on LinkedIn Learning
Otherwise you are right to say that a data model is not very efficient without DAX. For my part, I wanted to talk about performance for the stability and speed of data processing side

Thanks also to for the new measure that I will test on my model.

I will come back to a question about transforming my last file but in the Power query section

And a last question please, I have trouble distinguishing between single directional filter and bi-directional filter. Do you please have a video or a concrete example explaining the 2 options ?

Thank you in advance

Good evening to you


Return to “Power BI Desktop/Service”

Who is online

Users browsing this forum: No registered users and 1 guest