•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
[DAX/Data Modeling] filtering years and calculating Measures across tables
Our take
Are you feeling bogged down by the complexities of data modeling in Excel? If you’re looking to filter years effectively or calculate measures across tables, you’re not alone. Many users encounter challenges when trying to harness the full power of DAX in Power Pivot. In this introduction, we’ll explore how to filter your data to focus solely on 2020 and 2021, and we’ll break down the best practices for calculating Total Sales seamlessly across related tables.
Hi everyone,
I'm working on a data model in Excel (Power Pivot) and I'm stuck on two specific issues. I’m relatively new to DAX and would love some guidance.
Problem 1: Filtering Years I want to restrict my data/report to show only 2020 and 2021. I need to exclude 2022 entirely from the calculation. Is it better to do this via a Filter in the Pivot Table, or should I bake this logic into a DAX measure using CALCULATE?
Problem 2: Calculating across tables I need to calculate Total Sales, but the data is split:
- Table A (Sales): Contains
Units Sold. - Table B (Products): Contains
Unit Price. There is a relationship between these tables based onProductID. How do I write a measure that pulls the price from Table B to multiply by the units in Table A? I've tried a few things, but I keep getting errors because they are in different tables.
Any help or “best practice” advice would be greatly appreciated!
All files are from Excel is fun YouTube channel
the mention sheets are R DM DAX and HW4
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Pro Tip: You can lookup TEXT values from Data Model using CUBEVALUE (No Pivot Tables needed)Hi everyone, I've been working on optimizing some heavy Excel dashboards recently. I wanted to get away from VLOOKUPs and hidden Pivot Tables because the file was getting too slow and crashing with >1M rows. I switched to using CUBE functions to pull data directly from the Data Model into a custom layout. It works great for numbers, but I hit a wall when trying to pull **text values** (like a "Salesperson Name" based on an Order ID). Standard CUBE functions usually return numbers or error out with text. I figured out a workaround using a specific DAX measure logic and wanted to share it with the community. **The Solution:*\* Instead of just referencing the column directly, you need to create a Measure in Power Pivot that forces a text return. **Scenario A: Lookup within the same table*\* If you just need a text value from the row you are filtering: MeasureName := FIRSTNONBLANK('Table'[Column], 1) **Scenario B: Lookup from a related table (The "Filter Propagation" fix)*\* If you are filtering by Orders but want to see the Salesperson from a related 'People' table, standard filters won't flow "uphill". You need to force it: MeasureName := CALCULATE(FIRSTNONBLANK('People'[Salesperson], 1), 'Orders') **The Excel Formula:*\* Then, you can call this measure inside Excel using CUBEVALUE. It will look something like this: =CUBEVALUE("ThisWorkbookDataModel", "[Measures].[MeasureName]", "[Orders].[ID].&[" & A2 & "]") This returns the text string dynamically based on your cell value (A2). It made my dashboard significantly faster and cleaner. **Video Guide:*\* I recorded a short tutorial explaining the logic and showing the setup step-by-step if you want to see it in action: https://youtu.be/D2fCKXoGVb0 Hope this helps anyone stuck with rigid Pivot Tables! submitted by /u/Small-Camera-4348 [link] [comments]
- Dynamic Reference to Data in Pivot Table (part of data model)Hi! I have an excel with a big source data table (66k rows). I use pivot tables to summarize the data, and as normal pivot tables didn't show the data correctly, I always selected "add data to data model", and then it worked. Allthough the pivot tables look the same, they seem to work quite differently. I now want to look up certain values in these new pivot tables. Specifically, I have a table with DoI on one axis and the year on the other axis, and I want dynamic references, so that I can drag the formula. How do I need to write it? The table is directly linked to charts on a powerpoint, that's why I need it - there are some charts that are too complicated to link it directly to a pivot table. Here's an example, the X$8 and the $W9 are the references I would usually use. =GETPIVOTDATA("[Measures].[Sum of Value]",$D$8, "[PowerQuery_ALL_DATA_LONG_FORMAT].[Data Type]","[PowerQuery_ALL_DATA_LONG_FORMAT].[Data Type].&[Main Data]", "[PowerQuery_ALL_DATA_LONG_FORMAT].[Year]","[PowerQuery_ALL_DATA_LONG_FORMAT].[Year].&[X$8]", "[PowerQuery_ALL_DATA_LONG_FORMAT].[Status]","[PowerQuery_ALL_DATA_LONG_FORMAT].[Status].&[$W9]") Thanks for your help! Edit: I use MS 365, excel version 2511 Edit: this is how I would write it for a normal Pivot table, but it doesn't work in this case: =GETPIVOTDATA("Value",$D$8,"Data Type","Main Data","Status",$W9,"Year",X$8) submitted by /u/New-Lingonberry9322 [link] [comments]
- How do I Maximize File EfficiencyI work with data sets that I typically look at forecast by year. Currently when I look at 2026 and 2027 it is rougly 1.4M lines of data. I have to put these in two separate data pulls and tables. Then I have six different customers included in this data. so I have to create 6 tabs with six diffrent pivot tables for them to look at. This has created a massive file that lags just to open, save or close so I really have two questions and am open to suggestions. Would it be better to store the data in one worksheet and then link a second worksheet that just has the pivot tables and separated look? If so how would I creat that link? Can you explain to me like I am 5 how I would use power query to combine the 26 and 27 table so that they could be in the same pivot table? Every column in both are identical. submitted by /u/dcal69 [link] [comments]
- Sync or map data of two automated columns to the filtering systems of other columnsContext: The automated columns are C (Assigned Codes) and D (Positions). These were transferred from one workbook to another, which is this sheet you're seeing. I used the dynamic array filter function because it has to update in real time, as instructed by my manager. Example, my formula for column C: =FILTER([practicing.xlsx]Sheet1!B:B,[practicing.xlsx]Sheet1!B:B<>"",""") Thus, once the source workbook has more data, it can automatically show in this sheet. Reasons for not using alternatives: Power Query - it's not entirely automatic due to the load every x minutes, and the source workbook has to be closed for it to load in the destination workbook. Power Automate - blocked by my company The Problem: Column C and D aren't linked with the filtering systems of Column A (country) and Column B (Leader Assigned). For example, if the country USA is filtered/selected, then its assigned codes and positions should show. The issue is that their country code (starts with "US") and position, IT, are placed in different rows. If the USA is selected, it will only show rows C3-C4 & D3-D4, which is incorrect. https://preview.redd.it/ursej4pq8jxg1.png?width=916&format=png&auto=webp&s=272aa9d905e6d9e5277c70accc67e2614cc03dbd What I'm looking for: My assigned codes and positions already contain formulas (dynamic array filter function), so using another formula for these columns or in one cell can't be done (I suppose). Is there any way to map the C and D columns to the filtering systems for columns A and B? What I tried doing: Advanced filter - it adds a whole new table, but this sadly isn't what I'm looking for with my data. I want to just use the columns that I have now Custom filter - used the text filter -> begins with. It helps with filtering columns C and D for sure, but it doesn't remap the rows, so the data for columns A and B will appear inaccurate. Please let me know if I am also doing something wrong with what I've tried or done. Thank you in advance, and let me know if anything is unclear. This would really mean a lot to me. I am also open to chatting more! :)) submitted by /u/jeankrstein [link] [comments]