Help! explain this measure and why it isn't working consistently!!
I have a report that someone else made and they are not seeing some data show in the visuals that they would expect to see. After investigating I found out that the measures are behaving strangely. Basically there are 3 measures. One determines if there are any items on a SalesOrder that have a synthesisDepartment of "LEU Main - Tubes" : HasLEUMainTubes = VAR CurrentSalesOrder = MAX('vwOperationsBacklog'[SalesOrder]) RETURN CALCULATE ( COUNTROWS ( 'vwOperationsBacklog' ), 'vwOperationsBacklog'[SalesOrder] = CurrentSalesOrder, 'vwOperationsBacklog'[SynthesisDepartment] = "LEU Main - Tubes" ) One determines if there are any items on a SalesOrder that have a synthesisDepartment of "LEU Platinum" : HasLEUPlatinum = VAR CurrentSalesOrder = MAX('vwOperationsBacklog'[SalesOrder]) RETURN CALCULATE ( COUNTROWS ( 'vwOperationsBacklog' ), 'vwOperationsBacklog'[SalesOrder] = CurrentSalesOrder, 'vwOperationsBacklog'[SynthesisDepartment] = "LEU Platinum" ) And the third measure determines if the SalesOrder has items from both departments: HasBothLEUMainTubesAndPlatinum =IF ( [HasLEUMainTubes] > 0 && [HasLEUPlatinum] > 0, 1, 0) The visual is a table that displays a list of SalesOrders that have both Main and Platinum items. However, the users only want to see $ values and specific products that are from Main and have applied a visual level filter of Synthesis Department = "LEU Main - Tubes". Here is the issue: In most cases, this works out fine. Example order 9558544 which has 8 items in each of the two departments shows up in my main visual with only $ associated to the Main department. BUT example order 21583399 does NOT show up in the table even though it has 2 Platinum items and 3 Main items. I created a quick look at these 2 orders and the results of the 3 measures below (separated by Synth Dept) As soon as I apply a visual level filter to that same table (setting Dept = Leu Main - Tubes), this is the result: WHY DOES ONE ORDER WORK DIFFERENTLY FROM THE OTHER????? I also know I could probably come up with a better way of doing this (I did not develop these measures, a user did) but I ultimately want to know why it functions like this.
How to sum a rate from a dimension table only for the keys present in my fact table?
Hi Power BI Community, I have a classic star schema model with a fact table and a dimension table. My dimension table includes a numeric rate/price field (DiscountPlanEffectivePrice) and a key called DateDiscountPlanMeterId. This key is also present in my fact table, and it's used for joining the two tables. In my matrix visual, I’m grouping by various topology fields (like region, offer, pricing model, etc.) from my fact table, and I want to display the sum of the unique DiscountPlanEffectivePrice values from the dimension table, but only for the keys that actually exist in the current filter context of my fact table. My goal is to: Avoid summing all rates from the dimension, and Avoid duplicating the same rate for every related fact row. I’ve tried DAX measures like: Sum of Effective Discount Prices = SUMX ( VALUES(Fct_EA_AmortizedCosts[DateDiscountPlanMeterId]), CALCULATE( SELECTEDVALUE(Dim_EA_AmortizedCosts_DiscountPlans[DiscountPlanEffectivePrice]) ) ) and also attempted using SUMMARIZE, but I’m either getting blank results or incorrect totals. The relationship between the tables is active and based on DateDiscountPlanMeterId. How can I correctly sum only the unique rates from the dimension for keys that are present in my fact table’s current context? Is there a better DAX pattern, or am I missing something in the way context or relationships should be handled for this scenario? Any suggestions or guidance would be greatly appreciated! Thanks in advance! Jacek
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
Deltas within a Measure (?)
Hello all, again, I need help because I am totally lost: with the help of this forum, I was able to create this chart, and there is now only missing, to show the deltas between the periods (-15% between 2020 and 2021 i.e.). But I do not have an idea, how to calculate it, because to be able to create that chart, I have the values in one measure: YaQ_Volume = SWITCH( SELECTEDVALUE('Unrelated Date'[Category]), "Year",CALCULATE([SalesFinal],FILTER(VALUES(Output[Yr]),FORMAT(Output[Yr],"#")In VALUES('Unrelated Date'[Period]))), "Quarter",CALCULATE([SalesFinal],KEEPFILTERS(TREATAS(VALUES('Unrelated Date'[Period]),Output[QYear])), KEEPFILTERS(Output[Yr]=MAX(Output[Yr])))) Is there a way to insert now also the deltas between the periods? Thank you for any tipp and advice.Show latest value based on date and other criteria (measure or new column)
Hi, I am trying to create a measure or column that will allow me to easily bring the latest value into my visuals. Here is some example data; the highlighted column is what I'm trying to create. The end goal is to show the most recent sale as a flat line across a graph, so I can compare historical sales to the most recent one visually, by product. Also, the date needs to be dynamic so it automatically updates the values when fresh data is loaded. Thanks!
