My Matrix Table Keeps Showing More than X rows despite Measures - How to fix?
Hi - Currently my table (named Overview) is displaying Sites (via distinct site table) and Customers (via distinct customer table) as the Matrix Table's rows and the overall store sales + top 5 customer sales (depending on the time period picked). Data sheet hyperlink PBIX file hyperlink I'm experiencing a problem where: 1. I can't seem to make a Top/Bottom 20 stores slicer (based off sales) 2. Adding Date columns to my Matrix Table rows removes my top 5 Customers row condition and instead shows 6 or more customer rows/exceeds query resources To illustrate what i'm doing currently - I made the table's sales value by these measures: Total Sales = SUM( Overview[Sales]) Then i used this measure as the Matrix Table value: MEASURE-SALES = IF( ISINSCOPE( 'Unique Customers'[Customer] ), [Total Sales], CALCULATE( [Total Sales], REMOVEFILTERS( 'Unique Customers'[Customer] ) ) ) As mentioned before - i also made the table show each store's top 5 customers via this rank: Customer Rank = VAR BaseTable = ADDCOLUMNS( CALCULATETABLE( SUMMARIZE( Overview, 'Unique Customers'[Customer], 'Unique Stores'[Store] ), REMOVEFILTERS( 'Unique Customers' ) ), "@val", [Total Sales] ) VAR Result = RANK( SKIP, BaseTable, ORDERBY( [@val], DESC ), PARTITIONBY( 'Unique Stores'[Store] ) ) RETURN Result then i set this measure as = 1 in the table's filter Customer is visible = VAR CustomerRank = [Customer Rank] VAR Result = IF( ( CustomerRank <= 5 && NOT ISBLANK( CustomerRank ) ) || NOT ISINSCOPE( 'Unique Customers'[Customer] ), 1 ) RETURN Result My date table is below for reference (capped at current month for YOY measure purposes not mentioned above): Date = ADDCOLUMNS ( CALENDAR ( DATE (2023, 1, 1), DATE (2025, 4, 30) ), "Year", YEAR([Date]), "Year-Month", FORMAT([Date], "yyyy-MM"), "Year-Month sort", EOMONTH([Date], 0) ) Would like if there was a test file sample on this if possible as i keep getting stuck/written measures i recieve return an error- the common problems i'm running into for 2. as mentioned before is: - Query exceeds resource when Year-Month is added - Rows exceed 5 customers when "Year" column is added / time range on slicer is picked
Filter two tables on Multiple Criteria
Hello! this might be confusing and I can't share any data so I will do my best! I have two tables: SynthesizedItems and Failures. I have a report page dedicated to fail rate. This is just the number of fails/number of synthesized items. (measures in the failure table) Originally, I linked the two tables to a common date table. this allowed me to see the fail rate adjusted based on the users filtering by date. Then, I realized I needed to be able to break this down further. I added an additional table called DepartmentFilter and linked it to department in both the fail and synthesis tables. this seems to work. Now my question. My report has SEVERAL factors involved that users can slice their data by (all coming from the failure table) and I noticed that if the user filters to a certain customer, the fail table will filter but not the synthesized items table. (makes sense because they are not related, although customer exists as a field in both tables). Is it possible to make this method work where any filter the user applies will affect BOTH tables and therefore give an accurate rate? Is there a limit to how many common "filter" tables I can create and link to both? Is there a way to do it other than creating tables for each field? really trying to avoid this since it would be about 10 fields.
Help with FIFO logic with two Tables in DirectQuery
Hello, I'm struggling with creating a way to measure and/or visualize if a Load Unit that has been delivered to us, was also unloaded first. Deliveries have Priority 1-3 so what we want is, that the first delivery arrived is also the one that is unloaded first. I'm stuck with working in DQ and have to tables to do this- Transports Table and Unloading Table Transports Table has the Columns LoadUnitId, Priority and ActualArrival Timestamp. Unloading Table has the column StarTime Unloading. Something like this: LoadUnitId Priority ActualArrival StartTime Unloading 1234 1 01.01.2025 08:00 01.01.2025 08:15 4321 2 01.01.2025 07:35 01.01.2025 07:56 2134 1 01.01.2025 07:15 01.01.2025 08:19 What kind of expression can be used to check if a Load Unit that has the same priority, was delivered before another one, but unloaded later. I've been thinking of using Ranks but DirectQuery is a bit tricky and won't allow that. Maybe Flags would do the trick but I'm unsure how to use them in this case exactly. Any help would be highly appreciated. Thank you very much 🙂
