Create a visual table from many-to-many relationships connected sources and create subtotals
Hi, I'm having some difficulty constructing a visual table, with sources with many-to-many relationships, Let me build the scenario, First, I have the following fixed source table, for which I use the column "VALUE" as a reference, this table has duplicates rows, Table1 SUB-ID_ MONTH__ VALUE__ AB1 JAN 11934 AB1 JAN 11934 XX1 JAN 6576 XX1 JAN 6576 XX2 JAN 4789 XX3 JAN 4789 YY2 JAN 6498 YY2 JAN 6498 YY3 JAN 3333 YY4 JAN 1242 AB1 FEB 11930 AB1 FEB 11930 XX1 FEB 6573 XX1 FEB 6573 XX2 FEB 6000 XX3 FEB 7000 YY2 FEB 3500 YY2 FEB 3500 YY3 FEB 500 YY4 FEB 100 Then I have this second source table, variable, which gives me the "VALUE2", this table does not contain duplicates, Table2 ID_ SUB-ID_ CATEGORY_ VALUE2_ 1 AB1 CAT-1 1000 2 AB1 CAT-1 1000 3 AB1 CAT-2 3000 4 XX1 CAT-2 4000 5 XX1 CAT-3 5000 6 XX1 CAT-3 5000 7 YY2 CAT-4 6000 8 YY3 CAT-4 7000 After that, I would connect both tables 1 and 2, by the SUB-ID column, in this case, it would be a many-to-many relationship, After combining both sources, I would like to construct a new table (Table3 - Visual), using Table2 as a base, and I would be adding the column VALUE from Table1, But with one particular detail, I would like to insert also a column MONTH and use it as a filter to return the results based For example, Table3 Or Table3 And finally, after getting the Table3, I would construct a summarized Table4, using the column "CATEGORY" as a reference for subtotals, Table4 This problem seems simple by using Excel, but seems very tricky to resolve using PowerBI, I've tried to explain in the most simple and effective way here, I would appreciate it if anyone knows how to solve this, Thanks!Is it possible to use a visualization/slicer, that edits a DAX formula in the Data tab?
Hello, I want to use a slicer in the Reports tab that would allow a user to change a varible that would subsequently change a column formula created in the Data tab. For Example, the below image shows a date slider (let's call this date variable "SlicerDate"). This would be a visualization in the reports tab that any user can interact with. I want to create an IF statement in the Data tab that states, IF(X<SlicerDate, "Earlier", IF(X>SlicerDate, "Later")). Although the actual column formula in the Data tab would not change, the "SlicerDate" within the formula would change according to the date applied to the slicer. The reason for this is there are many people who will view this report themselves and would like to adust the returned variables based on a specific date, but not all people will know how to change data portion of the PBIX file or online file. I want users to be able to change the variable returned in the Data tab without leaving the Reports tab. Any assistance or suggestion is greatly appreciated. Thank you!
Dynamic Table based on Search
Dear all users I am new to PowerBi and taking an online course. However, my management wants something, and I am unsure I can solve it. Furthermore, I am having trouble finding a way to solve it, either using DAX or Power Query. Here is a breakdown of my current situation with Scrubbed tables. We are a manufacturing company with a base ERP system; I have taken a dump of information from different modules. My task is to create a graph showing me the item's current stock at any given date. Present Stock: This only shows the Present Stock current date. ( 1/18/2022 ) Item Code Item Name Current Stock ELESTA300001 GI REDUCER SOCKET 3"X2" BSP 50 LBFGRS300200 GI TEE 2" BSP 45 Material Receive Now: This showed when the Factory received each item Item Code Item Name MRN QTY Date ELESTA300001 GI REDUCER SOCKET 3"X2" BSP 100 1/10/2022 LBFGRS300200 GI TEE 2" BSP 100 1/10/2022 Material Issue: This shows that the store department issued this item to the end user reduces the stock effect. Item Code Item Name Issue QTY Date ELESTA300001 GI REDUCER SOCKET 3"X2" BSP 60 1/15/2022 LBFGRS300200 GI TEE 2" BSP 50 1/5/2022 Based on the above information, it should create an auto table based on the item been searched; here is an example of the result I am trying to achieve. Item ELESTA300001 Date MRN Issue Present Stock 01-Jan-22 10 02-Jan-22 10 03-Jan-22 10 04-Jan-22 10 05-Jan-22 10 06-Jan-22 10 07-Jan-22 10 08-Jan-22 10 09-Jan-22 10 10-Jan-22 100 110 11-Jan-22 110 12-Jan-22 110 13-Jan-22 110 14-Jan-22 110 15-Jan-22 60 50 16-Jan-22 50 17-Jan-22 50 18-Jan-22 50
Dynamic virtual summarized table
Hi, is there a way to write a DAX measure to obtain a dynamic virtual table that will give me the desire result as in below scenario? Basically the result I would want is the final Dynamic virtual table from rows 21 to 28, which can be dynamically filtered accordingly to visual filter context. If I have a visual (lets say a matrix table), and at one cell in that visual is Period 2022-Q1, I can have a table variable that changes accordingly to current filter context (which means I get rows 22 to 24) in that filter context. I tried to use SUMMARIZE, but it does not allow me to bring in columns from 2 different tables in 1 measure. I also tried SUMMARIZECOLUMNS but it cannot result in a dynamic virtual table that will change according to the filter context within the visual.Can we dynamically populate a calculated table according to the selected date range.
Hi, Can we dynamically populate a calculated table according to the selected date range. For Eg. Date, Id, Name 1 Jan 2020, 1, A 2 Jan 2020, 1,A 3 Jan 2020, 1, B 4 Jan 2020, 1,B 1 Jan 2020, 2, AA 2 Jan 2020, 2,AA 3 Jan 2020, 2, BB 4 Jan 2020, 2,BC With the above data I need to populate a dynamic table where it retrieves only the values of selected date in a slicer. Also note that my scenario doesn't allow date column as a slicer. So I need to pass this selected value as a measure which filters the dynamic table. Regards, Jishnu
Creating a dynamic table based on on-screen filters
I would like to measure to what extend items are available for a certain project within certain types of storage locations. I’ve got two fact tables (‘Required items per project’ and ‘Available items’) and two filter tables (‘Item’ and ‘Location’). For example, P1 requires 25x item 001 and there are 25 items available (100%) organization-wide. However, as our users would like to be able to filter on several specific combinations of locations to deliver items, dynamic filters are required. In case of only counting the available material with type of storage set at User (locations A,B, D, F) for project P1, the graphs should show 17/25 = 68% availability. So far, we’ve only succeeded in doing this with predetermined filters and merged tables in the powerquery. But as our list of locations is far longer than 6 and doing this with merged tables would result in an enormous amount of columns, we would like to see if it’s possible to use a filter on the board’s main screen for this operation. Could you please help us with the required steps to achieve this? We're now looking at the following steps: - Create dynamic table, based on on-screen location filter; - Create SUM measures (or column) for availabilty per item, based on aforementioned dynamic table; - Calculate availability (Available items / required items). Kind regards, Stephan
Create dynamic subset of data
Hi, I have a very large dataset (containing around 1 billion rows). Specific rows will be active based on several filters, causing the different rows to be dynamically active (by a key). I cannot apply them in an active relationship, as there are several filters making the selection. The active dataset will always be around 14.000 rows (much better to work with). Is it possible to create a new dynamic table only with the active rows, that I can then apply dax to? Thanks in advance! My ques
