Distinct count total is correct, but the columns are not
Hi, I am new to power BI and need some help. I have a list of patient IDs and I want to get the total distinct patients per fiscal year broken down by fiscal period, but when I put it in a table with columns representing the periods, it gives me the distinct total per period not per year, which is not what I want. Only the grand total in the table is correct. e.g. Here is what I'm getting for 2023 (using a slicer): Here's what I want: Again, to clarify, each patient should only be counted once per year, and should only be counted in the first period they appear. In my dates table, I have per row every date from 2022-2025 (Jour"), and the corresponding fiscal period (P#) and fiscal year (Annee 2). In my patient data table, the relevent columns would be the patient ID and appointment date (startdatetime). I have done a lot of research and none of what I've read is giving me the correct results. I can acheive the results when working with imported data, but I need to keep it as direct query so my options are more limited. I would appreciate any help you can give!
Measure to display query string parameter from report url
Hi all - We have created report based on dynamic m paramters using direct query. We want to pass query string paramter from report url and assign the value to dynamic m parameter. Currenlty, the functionality Works fine but only issue is we want to know what's parameter value being passed in the url . I created measure but it's not working fine. Can you please help. Example: Model: Result by passing below parameter to report (filter=ProductSearch/ProductIdFilter eq 'Job6789_8d02ac83dfd6') functionality works fine but Selected Value is displaying Blank SelectedValue = SELECTEDVALUE('ProductSearch'[ProductIdFilter]) Thanks, Abhiram
Searching a slicer in my Direct Query report blows it up (ORA-00904)
Good afternoon! Anonymous and I are building a report that performs a Direct Query to an Oracle database with a custom SQL query (that we feel is optimized) through a gateway. We originally had several search boxes with the Text Filter visual added, but when we would search we would immediately lose all the visuals. We replaced this with a slicer. It takes a moment for all the values to load, but when we select one it works. If we attempt to search the slicer at all, we lose the slicer visual with an ORA-00904 (invalid identifier) error. If the slicer has loaded all the selections, is it passing the search through SQL? I assumed it would just search in local memory. Do we need to do something funky to the SQL query? Is this just an optimization issue? Hopefully we're not the only people fighting this. Thanks in advance!Dynamic date based revenue calculation
Hi, I have the following case: I am building a report based on an Excel file hosted on a SharePoint Online environment. The file basically contains CRM opportunity lines with creation date, modified date and expected revenue as relevant columns. I have also created a relatively standard calendar/date table. Since the Excel file is constantly being fed with new data I am looking for a way to dynamically calculate total expected revenues within a given timeframe (preferably determined by a date slicer). The selected date (or period like Q1 2022), would determine old_revenue as the sum of expected revenue in the period prior to the period selected and new_revenue as the sum of expected revenue in the selected time period. So far my research has led me to Dynamic M Query Parameters which looks like it could have been a viable solution, but since my data source is Excel based, I don't have the option to use DirectQuery. Another option I have succesfully tried is hard coding measures that calculate old_revenue as getting the first day of today's month and then summing the revenue of the month prior to that date but this would mean I would have to hard code all possible options (week, month, quarter and year).
Related in Direct Query Mode
Hello! I am trying to do basic calculation Related in the Direct Query mode: Sales Amount = RELATED('product'[Unit_Price]) * sales_streaming[Units] and i get the following error: Is there any work around to perform the calculation between two tables in the direct query mode? Thanks!
Direct Query optimization with date table
Hi everyone, I have a simplified model with a calender table [BLAllgemein Datum] and a Fact Table [BL_Vertrag FAKT ...] looking like this. The fact table has - 1 summable column "AnzahlLaufenderVertrag" and - 2 date columns [StatistischGueltigAbDatum] and [StatistischGueltigBisDatum] which indicate, in which date range the particular row is valid. The calender table includes the dates and some transformations on them, nothing special. All I want to do is to calculate and display this measure __AnzVerträgeBestand, which will calculate the sum of [AnzahlLaufenderVertrag] for each date in the calender table, under the condition that the date range in the fact table includes this date. __AnzVerträgeBestand = VAR _Date = MAX ( 'BLAllgemein Datum'[Datum] ) VAR _result = CALCULATE ( SUM ( 'BL_VERTRAG FAKT_BESTAND Table'[AnzahlLaufenderVertrag] ), 'BL_VERTRAG FAKT_BESTAND Table'[StatistischGueltigabDatum] <= _Date, 'BL_VERTRAG FAKT_BESTAND Table'[StatistischGueltigbisDatum] > _Date, REMOVEFILTERS ( 'BLAllgemein Datum' ) ) RETURN _result Both tables are located in an MS SQL database and I try to query them in direct query mode. Unfortunately, the query is very slow taking about 14 seconds on my small test fact table with about 100.000 rows. In production, there are about 20 million rows, and the query runs several minutes. I've done some research and noticed, that Power BI generates an extremely long an inefficient SQL query that looks like this: // Direct Query SELECT TOP (1000001) [semijoin1].[c1],[basetable0].[c43],SUM( CAST([a0] as BIGINT) ) AS [a0] FROM ( ( SELECT [t3].[StatistischGueltigabDatum] AS [c43],[t3].[StatistischGueltigbisDatum] AS [c44],[t3].[AnzahlLaufenderVertrag] AS [a0] FROM ( ( select [AnzahlLaufenderVertrag], [StatistischGueltigabDatum], [StatistischGueltigbisDatum] from [BL_VERTRAG].[FAKT_BESTAND] as [$Table] ) ) AS [t3] WHERE ( ([t3].[StatistischGueltigabDatum] IN (CAST( '20150205 00:00:00' AS datetime),CAST( '20140428 00:00:00' AS datetime),CAST( '20131111 00:00:00' AS datetime),CAST( '20140331 00:00:00' AS datetime),CAST( '20201001 00:00:00' AS datetime),CAST( '20080728 00:00:00' AS datetime),CAST( '20060401 00:00:00' AS datetime),CAST( '20191004 00:00:00' AS datetime),CAST( '20190601 00:00:00' AS datetime),CAST( '20150325 00:00:00' AS datetime),CAST( '20181116 00:00:00' AS datetime),CAST( '20160803 00:00:00' AS datetime) ,CAST( '20110908 00:00:00' AS datetime),CAST( '20100804 00:00:00' AS datetime),CAST( '20190410 00:00:00' AS datetime),CAST( '20210119 00:00:00' AS datetime),CAST( '20161027 00:00:00' AS datetime),CAST( '20180612 00:00:00' AS datetime),CAST( '20120201 00:00:00' AS datetime),CAST( '20120711 00:00:00' AS datetime),CAST( '20150616 00:00:00' AS datetime),CAST( '20140901 00:00:00' AS datetime),CAST( '20120301 00:00:00' AS datetime),CAST( '20150323 00:00:00' AS datetime),CAST( '20150820 00:00:00' AS datetime) ,CAST( '20090306 00:00:00' AS datetime),CAST( '20141223 00:00:00' AS datetime),CAST( '20141212 00:00:00' AS datetime),CAST( '20200328 00:00:00' AS datetime),CAST( '20090610 00:00:00' AS datetime),CAST( '20130820 00:00:00' AS datetime),CAST( '20200701 00:00:00' AS datetime),CAST( '20150411 00:00:00' AS datetime),CAST( '20110214 00:00:00' AS datetime),CAST( '20080703 00:00:00' AS datetime),CAST( '20210304 00:00:00' AS datetime),CAST( '20140602 00:00:00' AS datetime),CAST( '20191021 00:00:00' AS datetime) ,CAST( '20101001 00:00:00' AS datetime),CAST( '20190904 00:00:00' AS datetime),CAST( '20110610 00:00:00' AS datetime),CAST( '20120404 00:00:00' AS datetime),CAST( '20170418 00:00:00' AS datetime),CAST( '20060207 00:00:00' AS datetime),CAST( '20150422 00:00:00' AS datetime),CAST( '20191119 00:00:00' AS datetime),CAST( '20160905 00:00:00' AS datetime),CAST( '20190710 00:00:00' AS datetime),CAST( '20150930 00:00:00' AS datetime),CAST( '20210301 00:00:00' AS datetime),CAST( '20181203 00:00:00' AS datetime) ,CAST( '20190502 00:00:00' AS datetime),CAST( '20190312 00:00:00' AS datetime),CAST( '20100308 00:00:00' AS datetime),CAST( '20181115 00:00:00' AS datetime),CAST( '20080620 00:00:00' AS datetime),CAST( '20171012 00:00:00' AS datetime),CAST( '20170602 00:00:00' AS datetime),CAST( '20170111 00:00:00' AS datetime),CAST( '20170621 00:00:00' AS datetime),CAST( '20191017 00:00:00' AS datetime),CAST( '20120809 00:00:00' AS datetime),CAST( '20150708 00:00:00' AS datetime),CAST( '20150226 00:00:00' AS datetime) ,CAST( '20181213 00:00:00' AS datetime),CAST( '20200601 00:00:00' AS datetime),CAST( '20090615 00:00:00' AS datetime),CAST( '20150922 00:00:00' AS datetime),CAST( '20060901 00:00:00' AS datetime),CAST( '20160726 00:00:00' AS datetime),CAST( '20171209 00:00:00' AS datetime),CAST( '20140923 00:00:00' AS datetime),CAST( '20120703 00:00:00' AS datetime),CAST( '20190101 00:00:00' AS datetime),CAST( '20190409 00:00:00' AS datetime),CAST( '20100112 00:00:00' AS datetime),CAST( '20190515 00:00:00' AS datetime) ,CAST( '20170316 00:00:00' AS datetime),CAST( '20190902 00:00:00' AS datetime),CAST( '20160503 00:00:00' AS datetime),CAST( '20180524 00:00:00' AS datetime),CAST( '20101103 00:00:00' AS datetime),CAST( '20150910 00:00:00' AS datetime),CAST( '20121116 00:00:00' AS datetime),CAST( '20170201 00:00:00' AS datetime),CAST( '20131024 00:00:00' AS datetime),CAST( '20180111 00:00:00' AS datetime),CAST( '20210128 00:00:00' AS datetime),CAST( '20090304 00:00:00' AS datetime),CAST( '20180120 00:00:00' AS datetime) ,CAST( '20180829 00:00:00' AS datetime),CAST( '20180314 00:00:00' AS datetime),CAST( '20130701 00:00:00' AS datetime),CAST( '20080616 00:00:00' AS datetime),CAST( '20190201 00:00:00' AS datetime),CAST( '20170303 00:00:00' AS datetime),CAST( '20180717 00:00:00' AS datetime),CAST( '20110727 00:00:00' AS datetime),CAST( '20120725 00:00:00' AS datetime),CAST( '20130228 00:00:00' AS datetime),CAST( '20151015 00:00:00' AS datetime),CAST( '20161031 00:00:00' AS datetime),CAST( '20171001 00:00:00' AS datetime) ,CAST( '20201007 00:00:00' AS datetime),CAST( '20160707 00:00:00' AS datetime),CAST( '20170815 00:00:00' AS datetime),CAST( '20200203 00:00:00' AS datetime))) ) ) AS [basetable0] INNER JOIN ( (SELECT 44198 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44199 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44200 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44201 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44202 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44203 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL .... (SELECT 44560 AS [c1],CAST( '20220401 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44561 AS [c1],CAST( '20220401 00:00:00' AS datetime) AS [c44] ) UNION ALL (SELECT 44562 AS [c1],CAST( '20220401 00:00:00' AS datetime) AS [c44] ) )) AS [semijoin1] on ( ([semijoin1].[c44] = [basetable0].[c44]) ) ) GROUP BY [semijoin1].[c1],[basetable0].[c43] This is already very long, but in fact the query has about 7.000 rows with tons of Casts of datetime values. On the other hand, I have recreated the query in T-SQL myself, coming up with this solution: SELECT TOP (1000001) [t4].[Datum], SUM(CAST([t5].[AnzahlLaufenderVertrag] as BIGINT)) AS [a0] FROM (( select SUM([AnzahlLaufenderVertrag]) as AnzahlLaufenderVertrag, [StatistischGueltigabDatum], [StatistischGueltigbisDatum] from [RADaR_Sandbox].[BL_VERTRAG].[Fakt_Bestand] as [$Table] group by [StatistischGueltigabDatum], [StatistischGueltigbisDatum] ) AS [t5] LEFT OUTER JOIN ( select [$Table].[Datum] as [Datum], [$Table].[JahrMonat] as [JahrMonat] from [BLAllgemein].[Datum] as [$Table] WHERE Jahr = 2021 ) AS [t4] on ( [t5].[StatistischGueltigabDatum] <= [t4].[Datum] and [t5].[StatistischGueltigbisDatum] > [t4].[Datum] ) ) GROUP BY [t4].[Datum] order by [t4].[Datum] The latter query takes only a fraction of a second and produces exactly the same result as the long query generated by Power BI. Can I do anything (in the dax query, the data model or direclty in the database) so that PowerBI will find a more efficient way to calculate the desired result? Any help is appreciated!
