direct query connection

4 Topics

DAX Running total in Direct Query, but reset to zero each day, if running below zero
Hi Power BI Community, I have an issue, where I want to calculate a running total in a measure, so I'm able to get the primo and ultimo value of the stock quantity. This is a forecast of the stcok 14 days ahead of time, and to be sure that we won't run below zero on a given day. In the event of the running total going below zero, I need it to reset and use the 0 as the new starting point. Backstory I need to use Direct Query as the data source connection. I have therefore limitations that I cannot use a calculated column, since I cannot use CALCULATE in calculated columns due to Direct Query. I have no possibility to throw the calculation out of DAX and Measures and into Power Query, again due to Direct Query limitations. I don't have a possibility to put the calculation outside of DAX, so I need to find a solution within measures, that I can use. Where I'm at right now My data looks a bit like this: Item Date Primo Sales Purchase Ultimo 1 16-08-2024 1.429 771 0 658 1 17-08-2024 658 1136 0 -1136 1 18-08-2024 0 4 384 380 1 19-08-2024 380 594 0 -594 1 20-08-2024 0 630 0 -630 1 21-08-2024 0 659 0 -659 1 22-08-2024 0 821 0 -821 1 23-08-2024 0 167 0 -167 1 24-08-2024 0 4 0 -4 1 25-08-2024 0 3 0 -3 1 26-08-2024 0 0 0 0 1 27-08-2024 0 0 0 0 1 28-08-2024 0 0 0 0 1 29-08-2024 0 0 0 0 I have tried to put everything into my ultimo calculation, but I can also see that that is the source of the error. I have 3 measures that's just a sum of the data in the table: WarehouseflowSales = SUM(warehouseflow[sales]) WarehouseflowPurchase = SUM(warehouseflow[purchase]) WarehouseflowInventory = SUM(warehouseflow[inventory]) Then I have the measure for the Ultimo column: WarehouseflowUltimo = VAR MaxDate = MAX(warehouseflow[date]) VAR UltimoYesterday = CALCULATE( -[WarehouseflowSales] + [WarehouseflowPurchase], ALLEXCEPT(warehouseflow, warehouseflow[itemno]), warehouseflow[date] < MaxDate ) + [WarehouseflowInventory] RETURN IF( UltimoYesterday < 0, 0, UltimoYesterday + [WarehouseflowPurchase] - [WarehouseflowSales] ) The measure for the Primo column is just the ultimo value from the day before: WarehouseflowPrimo = VAR MaxDate = MAX(warehouseflow[date]) VAR UltimoYesterday = CALCULATE( [WarehouseflowUltimo], ALLEXCEPT(warehouseflow, warehouseflow[itemno]), warehouseflow[date] = MaxDate - 1 ) RETURN IF( MaxDate = TODAY(), [LagerflowInventory], IF( UltimoYesterday < 0, 0, UltimoYesterday ) ) I have tried to edit the measure to use a somewhat circular reference, but whereas I handle the calculation for the WarehouseflowUltimo measure by date. Meaning that if the the date is today, then I should add the WarehouseflowPurchase and subtract the WarehouseflowSales to the WarehouseflowInventory. If it is not today, then I should use the WarehouseflowPrimo instead of the WarehouseflowInventory. Do you have any suggestions as to how I can handle this? Possible solution I can see myself I have thought of a solution to create 14 different measures, since I need to handle 14 days within my forecast. Then I should be able to handle the issue day by day. I'm just a bit tied on hands and legs due to performance on this issue as well. I haven't tried it yet, but would like to hear your suggestions first 😊
Solved
Anonymous
1 year ago Place DAX Commands and Tips
1.9KViews
0likes
5Comments
Connection to PowerBI database not working in report
Hi all, I created a report in the PowerBI desktop (pbix) using excel files as data and a direct query data connection from another PowerBI database. When I publish the .pbix file into the Report Server, all visuals based on the excel files are fine. However for the visuals based on the PowerBI datavase I get this error when the data is loading: Activiteits-id6268f0f0-784c-4a1d-9dee-caf4ac72117a Aanvraag-idc2bae30a-6636-4465-af8b-ab45b4992c9d Correlatie-ida2ac0388-e6eb-0c1a-dec0-53a22b870fe2 TijdTue Jan 31 2023 15:48:37 GMT+0100 (Central European Standard Time) Serviceversie13.0.19866.57 Versie van client2301.2.11954-train URI clusterhttps://wabi-west-europe-d-primary-redirect.analysis.windows.net/ How can I make my visualisations appear again? By the way: I have a PowerBi Premium PU (per user) licence. Regards, Yvonne
Yvonne24
3 years ago Place Service
1.3KViews
0likes
5Comments
Total Sum with Different Columns In Direct Query
Hello, I'm having trouble performing dax formula to give me the total spend for each table (asianamerican,diverse,africanamerican,wbe,divattributes) with the total coming from the fiscspend table with a relationship by the tax id. The Facts table (fiscspnd) is a direct query connection. 1st Question For example, I'm looking to do Total Asian American Spend = Sum or Calculate? Asian Table [ethnicity] = "Asian Amerian"? I also tried to do related with no success. 2nd Question :After having Total Asian American Spend I want to know the % of the Total Spend (this dax formula works) Would just a simple % of Spend =total asianamerican spend / total spend? Thank you for your guidance!
Solved
Anonymous
3 years ago Place DAX Commands and Tips
508Views
0likes
1Comment
Publish to Power BI App
Hi power bi community. I have developed a report using power bi desktop then published to a workspace and then published to an App. The report uses a direct query connection to an azure sql database. My question is do consumers of the app require source data base access (to my azure sql db) to be able to view the visual in the published app? My users all have pro licences, and can access the app but the report visuals error when they select the report. I have one other report in the app based on a import connection and that works fine. Also rather than potentially providing a large number of users with source db access is there a better way to resolve this? Many thanks for any help or advice.
Solved
Paullangdell
5 years ago Place Desktop
2.1KViews
0likes
1Comment