Forum Discussion
Data Weird Problem
Hello,
I have a problem using power BI direct quiery.
I'm working with data date, and even if in power BI editor quiery the values are recognized as a date, when I use the data for the creation of contents, they are seen as nominal data. Losing the possibility to select and show just the months for example..
How can I do? Thanks in advance 🙂
Hi, Anonymous
There is no built-in date hierarchy when use DirectQuery. You may try creating a hierarchy manually as below.
Temp:
You may create two calculated column as below.
Year = YEAR([Date])Month = MONTH([Date])Then you could right-click the 'Year' column=>'new hierarchy'=>drag 'Month' column into the hierarchy.
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
If I understand you correct you're looking for something similar to Excel filters where you can select/deselect the entire month or year when filtering date column. This is now how it works in Power Query. To filter your data by year/month without manual code editing use Date Filters menu:
For more advanced filtering you can edit M code manually to add a required combination of Date.Year(), Date.Month() and/or other Date functions.
Thanks avatorl for the reply. I try to explain me better.
The main problem is when I work with the direct query. My goal is to create a graph like this one:
In which my data are divided per month, and this I can easily do it with the static data, because the value of the date are recognized as such. And I can easily filter directly from the axis. (like this)
On the contrary, when using direct query, it does not recognize the date! And is almost impossible to me show the division per month but only per day! And I have something like this!
I also tried coding, but it says to me that is impossible using direct query.
It's all the morning I look for this solution, but I can't find it the appropriate one...
I'll be glad if you can help me!!!Hi, Anonymous
There is no built-in date hierarchy when use DirectQuery. You may try creating a hierarchy manually as below.
Temp:
You may create two calculated column as below.
Year = YEAR([Date])Month = MONTH([Date])Then you could right-click the 'Year' column=>'new hierarchy'=>drag 'Month' column into the hierarchy.
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
