Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there! I have a lot of columns with datetime that i want to see every count. I getting them from sql query to postgres and now i want to filter my summs with one filter on daterange. How can I do it?
I've tried to do that with 2 datetime params but it doesnt work
let
Start=DateTime.ToText(StartDate),
End=DateTime.ToText(VEndDate),
Source = PostgreSQL.Database("host", "dbName",
[Query="select count(cv.""Date1"") as Date1Cnt
, count(cv.""Date2"") as Date2Cnt
, count(cv.""Date3"") as Date3Cnt
from schemeName.""TableName"" as cv
where (cv.""Date1"" >= TO_TIMESTAMP('" &Start& "', 'dd.mm.yyyy hh24:mi') and cv.""Date1"" < TO_TIMESTAMP('" &End& "', 'dd.mm.yyyy hh24:mi') or cv.""Date1"" is null)
and (cv.""Date2"" >= TO_TIMESTAMP('" &Start& "', 'dd.mm.yyyy hh24:mi') and cv.""Date2"" < TO_TIMESTAMP('" &End& "', 'dd.mm.yyyy hh24:mi') or cv.""Date2"" is null)
and (cv.""Date3"" >= TO_TIMESTAMP('" &Start& "', 'dd.mm.yyyy hh24:mi') and cv.""Date3"" < TO_TIMESTAMP('" &End& "', 'dd.mm.yyyy hh24:mi') or cv.""Date3"" is null)
"])
in
Source
And there is sample of filtering in sql 😃
After you verify your sql works well in Postgres, you may want to try Value.NativeQuery() function; the advantage is straightforward, you can 100% copy the sql from Postgres. For your reference, here's an article on passing parameters to sql in Value.NativeQuery.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
also i've tred to do from that example , but i have an error: Query (2,14) Parametr M "EndDate" undefined in model type or parametr M cannot be used (sry for translation, my English is bad)
Hi @hollowyourdream ,
Have you defined and referenced the ‘StartDate’ and'EndDate’ parameters correctly?
For example, the data type of the parameters?
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yeah, i've seen this manual, but it is not work(
Hi @hollowyourdream ,
Considerations and limitations
It may be caused by this limitations.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
So, i've tried that with manual and it is not filtering(but i have bo error)
let
Источник = PostgreSQL.Database("localhost:5432", "HrMetrics"),
hr_metrics_CvInfo = Источник{[Schema="hr_metrics",Item="CvInfo"]}[Data],
#"FilteredRows" = Table.SelectRows(hr_metrics_CvInfo, each [ApprovedCvDate] >= VoronkaStartDate and [ApprovedCvDate] <= VoronkaEndDate)
in
#"FilteredRows"
And have i any variants to do filter on multiple columns in postgres?
Okay, i really using postgres, but i heard that i can fake it as possible source. Is it true? And if it is, how can i do that?
How can i pass it throw filter or slice? I need user to select daterange
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
106 | |
87 | |
73 | |
66 |
User | Count |
---|---|
124 | |
113 | |
98 | |
81 | |
72 |