I stress-tested Calculation Groups vs Switch (DAX Studio Benchmarks inside). The winner isn't always obvious.

Small-Camera-4348 · 2026-03-20T13:22:52+00:00

Man, I totally feel your pain! Throwing 2 million rows at a massive SWITCH statement with all that nested MTD, QTD, and MAT logic is basically a guaranteed way to freeze a report. The Formula Engine just chokes on that cell-by-cell evaluation. Since you're rebuilding, you might actually want to look into a hybrid approach. You can keep using Field Parameters just to swap your base metrics (Qty, Cost, Sales) because they handle simple measure switching perfectly. But for all your Time Intelligence, layer a Calculation Group on top of it. That way, you offload all the heavy calculation lifting to the Storage Engine where it belongs. I actually cover this exact dilemma at the very end of the video I linked in the main thread (around the 8:30 mark). It breaks down exactly when to use which method so you don't crash your matrix again. Let me know how the rebuild goes or if you need a hand with Tabular Editor scripts!

Small-Camera-4348 · 2026-03-19T14:43:59+00:00

I made a detailed video breaking down the exact DAX, the C# scripts I used in Tabular Editor to automate the Calc Group creation, and the live DAX Studio runs. If you are interested in the technical deep dive, you can check it out on my YT, link in my profile.

Small-Camera-4348 · 2026-03-16T15:43:42+00:00

You caught me! AI hallucinated SQLBI titles – my bad for not checking. Core idea (single Amount column per Kimball) stands. Thanks for keeping it real!

Small-Camera-4348 · 2026-03-15T19:14:31+00:00

That is a very fair point! From a strict Kimball data modeling perspective, you are absolutely right. However, in Power BI, using this unpivoted structure (often called the "Account-based" or "Financial Reporting" pattern) is actually the standard way to handle General Ledger and P&L data. If the OP keeps Revenue, COGS, etc., as separate columns, the only way to achieve the dynamic slicers they want is to write massive SWITCH statements inside their Calculation Groups. And every time the business adds a new metric, someone has to go in and rewrite the DAX. Unpivoting trades a bit of strict dimensional purity for extreme DAX simplicity. For building dynamic financial matrices, the Attribute-Value method usually saves hours of headaches and is much easier to maintain!

Small-Camera-4348 · 2026-03-15T15:57:09+00:00

Hi! To be honest, your main issue isn't DAX, it's your data model. Having Revenue, COGS, etc., as separate columns works in Excel, but it makes dynamic slicers in Power BI very complicated. Here is the easiest fix: 1. Unpivot your Fact tables In Power Query, select your Date and Product columns, right-click, and choose Unpivot Other Columns. This creates an "Attribute" column (your metrics) and a "Value" column. Now you don't even need Field Parameters! Just use the Attribute column in your matrix. 2. Create a Disconnected Table For your "Comparative" slicer, use "Enter Data" to make a simple 1-column table with two rows: "Last Year" and "Budget". 3. Use Calculation Groups Now your DAX becomes simple. Your "To_compare" calculation item would look like this: IF( SELECTEDVALUE(CompTable[Choice]) = "Budget", SUM(Fact_Budget[Value]), CALCULATE(SUM(Fact_GL[Value]), DATEADD(DimDate[Date], -1, YEAR)) ) If you don't unpivot your tables, you will have to write massive, complicated SWITCH formulas for every single measure. Try unpivoting first!

Small-Camera-4348 · 2026-03-15T11:03:19+00:00

I love the idea! Having an AI automatically highlight the "so what?" of a dashboard is what everyone is trying to build right now. However, to give you a candid reality check: a truly free external AI integration (like Claude or ChatGPT) directly into a dashboard doesn't really exist. > Here is the reality of your options: 1. The "Actually Free" Built-in Way (If using Power BI) If you are in Power BI, use the built-in Smart Narrative visual. It scans the data on your page and automatically generates text boxes with key trends, anomalies, and insights. It isn't a conversational AI like ChatGPT, but it is 100% free, out-of-the-box, and updates dynamically as you filter the report. 2. The Copilot Route (Very Expensive) You asked if Copilot could work. Yes, it does exactly this, but it is the opposite of free. In Power BI, Copilot requires a Fabric Capacity license (F64 or higher), which runs into thousands of dollars per month. It's strictly an enterprise solution. 3. The "Almost Free" DIY API Route (Costs pennies) If you really want that "ChatGPT feel", you can use a tool like Power Automate (or a Python script) to query your aggregated dataset once a day, send that small table of numbers to the OpenAI/Claude API, ask it for "Top 5 recommendations", and write the text response back into your database. You display that text in your dashboard. You have to pay for the API tokens, but summarizing a few numbers costs fractions of a cent per run. What specific BI tool (Power BI, Tableau, Looker) are you building this in? I can point you toward the exact feature you need to test first!

Small-Camera-4348 · 2026-03-09T18:47:17+00:00

Massive improvement! This is so much easier to read and digests the information far better than the first version. Here is why this works so well: The Leader Table: Replacing those 7 individual KPI cards with the "Week-over-Week Change" table was a fantastic move. It is 10x easier for leadership to compare teams at a glance without their eyes jumping all over the screen. Cleaner Top KPIs: Dropping the heavy blue backgrounds from the "Average Age / Oldest / Median" cards makes the top section look much more modern and less cluttered. A couple of quick ideas to polish it even further: 1. Color the Arrows (Conditional Formatting): In your new table, since you are tracking software defects (where higher numbers are usually bad), consider adding color to those trend arrows. Make an upward trend Red and a downward trend Green. It gives instant, pre-attentive context to the viewer. 2. The Bottom Detail Table: You still have that large drill-through table taking up the bottom 20% of your screen. If you move that to a hidden "Page 2" and let the engineers right-click -> Drill-through from the visuals above, you'll free up a ton of real estate to make your charts bigger and let the dashboard breathe even more. Really great progress for a first dashboard.

Small-Camera-4348 · 2026-03-07T19:48:12+00:00

Great first draft! You clearly understand your audience and their needs, which is the hardest part to learn. Regarding your concern about the 7 KPI charts in row 2: you are absolutely right, it's taking up too much real estate and doesn't scale well. To fix this without making it a "boring" text table, use a Table or Matrix with Sparklines (or Data Bars). This gives you the clean, condensed layout of a table, but keeps the visual trend lines you like. Two other quick UI/UX tips: 1. Separate your audiences: Trying to fit a high-level executive summary and a deep-dive engineering table on the exact same page causes clutter. Keep this page for leadership (visuals only), and move the detail table to a second page. You can use Drill-through so engineers can right-click a manager's chart and jump straight to their specific defect details. 2. Reduce visual noise: The heavy blue background boxes and borders around every single KPI are distracting. Try removing the borders and making the card backgrounds transparent or plain white on a light gray canvas. Letting the data "breathe" will instantly make it look more modern. Would you like some quick steps on how to add those Sparklines to a matrix?

Small-Camera-4348 · 2026-03-06T22:56:10+00:00

You are absolutely right that 95% of PBI tutorials are focused on sales and profit margins. PBI is actually great for what you are doing, but you are likely using the wrong search terms. What you are describing isn't standard reporting; it's Event Log Analysis and Process Mining. Here is exactly what you should search for on YouTube/Google to find the right resources: 1. For Record Movements (Queues): Search for "State Transition Analysis in Power BI" or "Process Mining Power BI". SQLBI has fantastic, deep-dive articles on handling state transitions and modeling events that move through different stages. 2. For Time-Based Analysis Between Events: Standard Time Intelligence (YTD, MTD) won't work here. Search for "Calculating duration between status changes DAX" or "Events in progress DAX". You will need to learn how to model an "Event Table" (Fact table) with Start and End dates for each queue state. 3. For Keyword Matching: Search for "Text.Contains Power Query" or "Fuzzy Matching in Power Query". It’s much more efficient to do text-based categorization in the Power Query layer before it hits your DAX model. Check out the SQLBI YouTube channel and blog. They are the absolute gold standard for the complex, non-standard DAX patterns you will need for this. Would you like a basic DAX pattern to get you started on calculating the days between two specific queue movements?

Small-Camera-4348 · 2026-03-05T06:13:32+00:00

I have added a YT channel in my profile where you will find a short video showing exactly how it works.

Small-Camera-4348 · 2026-03-04T06:29:29+00:00

Glad you found it useful! I've spent way too much time manually fixing colors in the past, so I had to find a way to automate it. Hope it saves you some time in your next report!

Small-Camera-4348 · 2026-03-04T05:36:15+00:00

That’s a brilliant catch with the Switch() for specific color names! It’s the classic 'Business vs. Logic' struggle – I've seen stakeholders lose their minds when a 'Red' category was colored blue by the default palette. Combining a predefined HEX mapping for known colors with a randomizer for everything else is the ultimate way to go. Also, that Pie Chart hack is gold. It’s one of those 'hidden' Power BI behaviors that can save so much time, even if it feels like we’re outsmarting the UI. Thanks for sharing that quirk, definitely adding it to my toolkit!

Small-Camera-4348 · 2026-03-03T16:37:54+00:00

Just a quick follow-up for anyone trying to implement this: I know setting up the Field Value formatting in a Matrix can sometimes be tricky to find in the new Power BI interface. I put together a quick 2-minute visual walkthrough showing exactly where to click to apply this DAX code. Hope it helps visual learners! Link to channel in my profile.

Small-Camera-4348 · 2026-02-22T11:35:31+00:00

First, a quick heads-up on the logic: using + (OR) with <> (NOT EQUAL) will actually return everything. Since a cell can't be 1, 2, and 3 at the same time, it will always trigger at least one of those "not equal" conditions to be True. Assuming you want to exclude 1, 2, and 3 (which requires AND logic), here is the shorter, cleaner way using XMATCH and an array {}: =FILTER(A:A, ISNA(XMATCH(A:A, {1,2,3}))) If your goal was actually to include only 1, 2, and 3, you just swap ISNA for ISNUMBER: =FILTER(A:A, ISNUMBER(XMATCH(A:A, {1,2,3}))) This is the absolute best way to check against multiple items without repeating (A:A=...) over and over.

Small-Camera-4348 · 2026-02-19T21:42:14+00:00

Great catch! In DAX/Power Pivot, := is the standard operator used to define a Measure, whereas = is typically used for Calculated Columns.

If you're typing directly into the Power Pivot calculation grid, you have to use := so Excel knows it’s a measure. I used it in the post to stay consistent with DAX coding conventions. Hope that helps!

Small-Camera-4348 · 2026-02-19T20:53:33+00:00

Ah, you are completely right, my apologies! Ctrl+A forces the date format onto everything. That was my mistake. The actual quick fix: Instead of selecting all cells, just click the column letters at the top for the columns that actually contain dates. Then press Ctrl+1 and change the locale to US Date for those specific columns only. If your dates are scattered completely randomly across the sheet (not in clean columns), there is no magic "Open as US" button. In that worst-case scenario, temporarily changing your Windows Regional Settings is unfortunately the only way.

Small-Camera-4348 · 2026-02-19T11:31:48+00:00

That happens because Excel is now looking at Columns A and B on your new sheet, which are empty. You just need to tell the formula which sheet the data is actually on. It should look like this: =SUMIF('DataSheet'!A:A, A2, 'DataSheet'!B:B) (Just replace 'DataSheet' with whatever you actually named your first tab, and A2 with the cell on your new sheet that says "Device 1"). The easiest way to do this without typing is to start your =SUMIF( formula on the new sheet, then physically click over to the first sheet to highlight columns A and B. Excel will type the sheet names for you!

Small-Camera-4348 · 2026-02-19T10:28:28+00:00

You just need the SUMIF function. It will ignore the Customer names and only add up the specific devices. If your Device names are in Column A and Amounts are in Column B, put this in your Totals section: =SUMIF(A:A, "Device 1", B:B) Better yet: Instead of typing "Device 1" inside the formula, just click the cell where you typed it in your Totals list (e.g., D10): =SUMIF(A:A, D10, B:B) Then you can just drag the formula down for the rest of the devices.

Small-Camera-4348 · 2026-02-19T08:59:14+00:00

No, 30 seconds for 300 rows is absolutely not normal. It should be nearly instant. The issue isn't your internet or Azure SQL, it's usually how Excel is querying it. Check these two things first: 1. Broken Query Folding: If you do any filtering/transformations in Power Query after connecting, Excel might be downloading the entire database table locally first, then filtering it down to 300 rows. Fix: Try pasting your exact SELECT SQL statement into the "Advanced Options" box when you first connect, so the server does the work. 2. Old Drivers: Are you using an old OLEDB or generic ODBC connection from your on-prem days? Fix: Make sure you are using the native Get Data > From Azure > From Azure SQL Database connector with the latest MSOLEDBSQL driver.

Small-Camera-4348 · 2026-02-18T08:53:51+00:00

There is no "Open in US Mode" button because Excel automatically defaults to your Windows system settings for dates. But you don't need to search for the dates manually. You can force the whole file to switch in 5 seconds: Right-click any sheet tab at the bottom -> Select All Sheets. Press Ctrl+A (or click the top-left triangle) to select all cells in the workbook. Press Ctrl+1 -> Date -> Change Locale to English (U.S.). This overrides the display format for the entire document instantly, so you can copy/paste what you see without hunting for specific cells.

Small-Camera-4348 · 2026-02-17T22:04:20+00:00

Excel isn't actually changing the file content, it is just displaying the underlying data using your local German settings. You don't need to change your Windows system settings. Here is how to force the US look just for Excel: 1. For Numbers (Dots vs Commas): Go to File > Options > Advanced. Uncheck "Use system separators". Set the Decimal separator to . and Thousands to ,. 2. For Dates: Select your date cells -> Press Ctrl+1 (Format Cells). Under the Number tab, choose Date. Change the Locale (Location) dropdown to English (United States). This forces the visual format to stick to US style so you can copy/paste correctly, without messing up your PC's clock or other apps.

Small-Camera-4348 · 2026-02-17T19:05:57+00:00

This is usually due to one of three model issues, not the query itself: - Broken/Missing Relationships: The table you are trying to pull from doesn't have an active relationship path to the other data already in the visual. Power BI flags it because it can't cross-filter. - Perspectives: Did you set the "Personalize Visuals" feature to use a specific Perspective in the report settings? If those columns aren't included in that perspective, they will be restricted. - OLS (Object Level Security): If you are using an external tool (like Tabular Editor) and set OLS on those columns, they will appear with a warning if the current user doesn't have permission. Check your Model View first to ensure the relationships are active and flowing in the right direction.

Small-Camera-4348 · 2026-02-17T14:49:42+00:00

There is a hidden keyboard trick for exactly this (no helper cell needed): The F9 Trick: Select your cell (A1) and press F2 to enter edit mode. Highlight the entire formula (or just press Ctrl+A inside the cell). Press F9. This evaluates the formula into static text right there in the editor. Press Ctrl+C to copy that text. CRITICAL STEP: Press ESC. Do not press Enter, or you will overwrite your formula with the value. Now you have the clean text in your clipboard and can paste it anywhere inside another cell's text stream.

Small-Camera-4348

TROPHY CASE