User Profile
lg_analyst
Helper I
Joined 6 years ago
User Widgets
Contributions
Re: Calculating column based on filter on another table
Hi amitchandak thank you for replying. I can't use the formula you suggest because, as you can see from the first screenshot, I'm summing data in one table (PRODUCTS) but based on filters from that and another table (ORDERS). Can you suggest a way to work around this? Thank youCalculating column based on filter on another table
Trying to simplify my model as much as possible: PRODUCTS table has a list of product_ids (which can be replicated), a season and a start and end date for the season ORDERS a list of orders, product_ids purchased, quantity and order_date DATE MASTER a calendar of the current and past year Ther is a One to Many relationship between DATE MASTER(Date) and ORDERS(order_date) On the PRODUCTS table I have this calculation: Where Max Filtered Date = MAX('DATE MASTER'[Date]) I'd like prod_quantity to be dynamic and change when a different date is selected from the date filter. In my example I set 31/07/2022 as max date and I'd like prod_quantity to return 3. I also tried creating a metric instead of the column but still can't get this to work. Is there a way to solve this? Output: ORDERS table: Thank you very muchRe: Connecting Big Query view with partitioning
Hi Jing, thank you for your answer. I can't disable the require partition filter because the table is also used by other users (other than Power BI) and the filter is necessary to try and contain costs as much as possible. Using Power Bi Desktop I have the same issueConnecting Big Query view with partitioning
Hi, I'm trying to connect to a BigQuery view which is linked to a table that is partitioned on orderDate and requires a filter on orderDate for the query to run. I'm connecting via the Power BI dataflow. When connecting to the actual table (orders) I am able to add a filter on orderDate and connect correctly. However, when attempting to connect to the view (orders_test) it returns an ODBC error. Advanced editor: let Source = GoogleBigQuery.Database([BillingProject = null, UseStorageApi = null, ConnectionTimeout = null, CommandTimeout = null]), Navigation = Source{[Name = "bi-stg"]}[Data], #"Navigation 1" = Navigation{[Name = "bi", Kind = "Schema"]}[Data], #"Navigation 2" = #"Navigation 1"{[Name = "orders_test", Kind = "View"]}[Data], #"Filtered rows" = Table.SelectRows(#"Navigation 2", each [i_orderDate] = #date(2022, 1, 4)) in #"Filtered rows" Error: DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: Cannot query over table 'bi-stg.bi.orders' without a filter over column(s) 'orderDate' that can be used for partition eliminationDetailsDataSourceKind = GoogleBigQuery DataSourcePath = GoogleBigQuery OdbcErrors = #table({"SQLState", "NativeError", "Message"}, {}) Note that the error message is referring to the original table (orders) and it's orderDate column, not to the view (orders_test) and it's orderDate column (i_orderDate) which I'm trying to query. I tried changing the Filtered Rows step and specifying orderDate instead of i_orderDate but receive the same error. It's assuming I need to add a filter to the original table even though I'm trying to query the view. Can anyone help? Thanks!Alternatives for filtering dataflows
Hi, I know that there is no possibility to have row level security for dataflows (does anyone know whether this is on the roadmap?) Is there however an alternative to to this with linked dataflows? I'm thinking a main dataflow with the complete dataset and then linked ones where the data is filtered by, let's say, market. So that when the market dataflow refresh they don't query the database again but they pull data from the main dataflow and this ends up making the market dataflow refresh quicker (than querying the database again but with a filtered query). Is something like this available either with a Pro or Premium per User accounts? ThanksRe: Single row Filtering affecting multiple rows
Hi v-yingjl , Unofrtunately, my data is meant for confidential use only. I'm attaching a screenshot taken directly from the model, maybe it will help. Here you can see two Order Numbers, two different orders. Each order can be splitted in different shipments, indeed, first order has 6 different Shipment Number, same for the second one. Each shipment can be assigned in different days, indeed Shipment Number 130882717 has been assigned on 06/04, all other shipments for the first orders have been assigned on 02/04. Same thing for the second order: shipment 130884263 has been assigned on 05/04, all other shipments for the second order have been assigned on 04/04. My goal is to have a filter allowing me to do the following: should I filter for 02/04 OR 06/04, I should be able to see all rows for the first order. Should I filter for 04/04 OR 05/04, I should be able to see all rows for the second order. The solution you've brought up really goes in this direction, but it only allows me to se all rows for each order if I filter for the earliest Order Assigned date, which is 02/04 for the first order, 04/04 for the second order. I need all Order Assigned dates for each Order Number to do so. If you have further questions, please feel free to be more specific about it. Thanks a lot.Re: Single row Filtering affecting multiple rows
Hi v-yingjl , Thanks for your answer. You model is now working, but it doesn't 100% provide what I need. Indeed, your solution only works when selecting the earliest Start (or End) Date for the same Order ID. Taking into account the attached table in your reply, my goal is to have all rows having Order ID = A even if I filter for Start Date '01/02/2021'. With your solution, this is only possible by filtering Start Date '01/01/2021'. As far as I'm understanding, the logic you use building visual_control measure cannot work in any other way tho. Thanks, EnricoRe: Single row Filtering affecting multiple rows
Hi v-yingjl , Thanks for you answer. I've attempted using the .pbix you've attached but the model doesn't work on my pc. I find it pretty weird, considering it works on yours. Might it be a PBI version issue? It seems strange to me but it's the only guess I can make. By the way, this is exactly the objective I want to achieve. ThanksRe: Single row Filtering affecting multiple rows
Hi v-yingjl , My expected output is a table such as the one I've screenshotted, it's going to be very similar in the visualization as well. I cannot filter through Order IDs, the request is being able to use Start Date and End Date in the way I've shown. Basically for each Start Date (or End Date) the user filters, I need to be able to show all the rows related to Order IDs that have at least one Start Date in the dates the user filters. My difficulty resides in the fact that I need to show rows belonging to other Start Dates as well, if they have an Order ID in common. Here's another example, basically I should be able to visualize both rows of the table wether I'm filtering for 01/01/2021 or 02/01/2021. The user needs to filter through Start or End Date, not using Order ID's list. Rows could be more: each Order ID might have more than two Shipment ID linked to it, with no precise limit. Order ID Shipment ID Start Date End Date A XXX 01/01/2021 10/01/2021 A YYY 02/01/2021 11/01/2021
