Debug DAX measure variables incl. virtual tables
Hi, I created an idea to be able to visually inspect the variables of a DAX measure, when the DAX measure is used in a table visual, line chart visual, etc. Please vote 😀 https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=a7a31361-2f40-ef11-b4ac-6045bdbf86b4
Rating system not giving me a total
I'm looking to create a rating system that will display the overall health of a division example; anything greater than 80% is 1 point, 50% - 79% will be .5 points, 49% and less is 0 points. i created the following dax which was able to give me the correct values, but i was not able to get the table to calculate a total, its giving me an average Overall point = VAR _Claims = SWITCH( TRUE(), [% of Claims Assigned] >= .80, 1, [% of Claims Assigned] >= .60 , .5, 0 ) VAR _Risk = SWITCH( TRUE(), [% of Risk Assigned] >= .80, 1, [% of Risk Assigned] >= .60 , .5, 0 ) VAR _Casualty = SWITCH( TRUE(), [% of Casualty Assigned] >= .80, 1, [% of Casualty Assigned] >= .60 , .5, 0 ) VAR _Property = SWITCH( TRUE(), [% of Property Assigned] >= .80, 1, [% of Property Assigned] >= .60 , .5, 0 ) VAR _Primary = SWITCH( TRUE(), [% of Primary Assigned] >= .80, 1, [% of Primary Assigned] >= .60 , .5, 0 ) VAR _Cyber = SWITCH( TRUE(), [% of Cyber Assigned] >= .80, 1, [% of Cyber Assigned] >= .60 , .5, 0 ) VAR _Total = CALCULATE(_Claims + _Risk + _Casualty + _Property + _Primary +_Cyber) RETURN _Claims + _Risk + _Casualty + _Property + _Primary +_Cyber Expected visual the total is 32.5 current visual is giving an average
Pass slicer date range to a variable
I am having trouble passing a slicer date range to a variable. I have two tables: 1. Before_Day_Key is used for my before slicer 2. After_Day_Key is used for my after slicer I have created variables that create the min and mix date for each of the slicers. Now I want to create a variable that has the date range of each slicer. What I am expecting is: VAR _periodBefore: 01/17/2023 - 03/20/2023 VAR _periodAfter: 03/28/2023 - 05/29/2023 Before_Day_Key table is here. After_Day_Key table is here. Can you help complete the statement: PED2 = VAR _minDateBefore = MIN('Before_Day_Key'[Business Date]) VAR _maxDateBefore = MAX('Before_Day_Key'[Business Date]) VAR _minDateAfter = MIN('After_Day_Key'[Business Date]) VAR _maxDateAfter = MAX('After_Day_Key'[Business Date]) VAR _periodBefore = VAR _periodAfter =Sum for last days of month per each category - how to simplify
Hello, I have a quite peculiar problem. I have a column with values that represents the state of Inventory for each Site (category). Which means that the most recent one value for each month is always last day per each site per month. Example for site 667 for november its going to be value 5 252 235.74 (31/12/2021) but for site 200 its going to be 79 967 894.18 (30/12/2021) The sum of those values should be 85 220 129.92 which is state of inventory for those two sites per december. I was able to calculate this with this measure: Inventory Cost = VAR _pretable = ADDCOLUMNS ( SUMMARIZE ( v_factinventorytransactions, v_dimdate[DateId], v_factinventorytransactions[SiteId] ), "InventoryCost", CALCULATE ( AVERAGE ( v_factinventorytransactions[RunningCost] ) ) ) VAR _table = FILTER ( _pretable, VAR _MaxDate = CALCULATE ( MAX ( v_factinventorytransactions[InventoryTransactionDateId] ), ALLSELECTED ( v_dimdate[DateId] ) ) RETURN v_dimdate[DateId] = _MaxDate ) RETURN SUMX ( _table, [InventoryCost] ) Which works perfectly but I'm wondering if it can be simplyfied. I want it to simplify, because when I want to use this measure inside another one that sums those Inventory Cost values per month for last 3 months and I have wrong answers. Which means that this Inventory Cost measure works but if I call out this measure in the one below it shows wrong numbers (but other measures, more simply ones work). Rolling3Months = VAR _EndDate = MAX(v_dimdate[Date]) VAR _Dates = DATESINPERIOD(v_dimdate[Date], _EndDate, -3, MONTH) VAR _Cost = [Inventory Cost] VAR _Inventory = SUMX(_Dates, CALCULATE(_Cost, ALL(v_dimdate[YearMonth]))) RETURN _Inventory I'm a little bit stuck and would be super appreciated when someone would pointed out my mistakes/errors here. I'm also providing sample power BI file with those. https://we.tl/t-eQSOYHm1ft Thank you
Date Filtered Calculation Not Working As Expected
I have three measures on a report that calculate stock value and the change in stock value on a monthly basis. They need to be receptive to two slicers; a date slicer and a Component Type slicer. They work by first establishing a variable date range and then using an IF statement, so that if a month is selected with the slicer they show 1) the selected month's value, 2) the preceding month's value, 3) the difference between them. If the date slicer is left blank, it shows the values for the two most recent months in the dataset. Here are the measure expressions for the two monthly values: Selected month's value = var _selectmonthbegin = eomonth(SELECTEDVALUE('Component Type Nov - Jan'[Date]),-1)+1 // begin of sliced month var _selectmonthend = eomonth(SELECTEDVALUE('Component Type Nov - Jan'[Date]),0) // end of sliced month var _currentmonthstart = eomonth(MAX('Component Type Nov - Jan'[Date]),-1)+1 // begin of most recent month var _currentmonthend = eomonth(MAX('Component Type Nov - Jan'[Date]),0) // end of most recent month return IF( not ISBLANK('Component Type Nov - Jan'[4. Selected month]), CALCULATE( SUM('Component Type Nov - Jan'[Total Stock Replacement cost]), FILTER(('Component Type Nov - Jan'),'Component Type Nov - Jan'[Date] >= _selectmonthbegin && 'Component Type Nov - Jan'[Date] <= _selectmonthend)) , CALCULATE( SUM('Component Type Nov - Jan'[Total Stock Replacement cost]), FILTER(('Component Type Nov - Jan'),'Component Type Nov - Jan'[Date] >= _currentmonthstart && 'Component Type Nov - Jan'[Date] <= _currentmonthend))) Month before selected value = var _lastmonthbegin = eomonth(SELECTEDVALUE('Component Type Nov - Jan'[Date]),-2)+1 // begin of month before var _lastmonthend = eomonth(SELECTEDVALUE('Component Type Nov - Jan'[Date]),-1) // end of month before var _onebeforestart = eomonth(MAX('Component Type Nov - Jan'[Date]),-2)+1 // begin of one before most recent month var _onebeforeend = eomonth(MAX('Component Type Nov - Jan'[Date]),-1) // end of one before most recent month return IF( not ISBLANK('Component Type Nov - Jan'[4. Selected month]), CALCULATE( SUM('Component Type Nov - Jan'[Total Stock Replacement cost]), FILTER(('Component Type Nov - Jan'),'Component Type Nov - Jan'[Date] >= _lastmonthbegin && 'Component Type Nov - Jan'[Date] <= _lastmonthend)) , CALCULATE( SUM('Component Type Nov - Jan'[Total Stock Replacement cost]), FILTER(('Component Type Nov - Jan'),'Component Type Nov - Jan'[Date] >= _onebeforestart && 'Component Type Nov - Jan'[Date] <= _onebeforeend))) What's confusing me is that despite having identical structure (I copied and pasted the code and just swapped the time parameters), the Month Before Selected measure does not behave the same way as the Selected Month measure. Selected Month works perfectly whether a month has been sliced or not, but Month Before Selected only works if no month is sliced - once you slice a month it returns (Blank). My immediate thought was that the variable date range wasn't working, but I have put them in their own measures and used callout cards to confirm that they do work properly. Adding ALL to the measure so that it's CALCULATE( SUM('Component Type Nov - Jan'[Total Stock Replacement cost]), FILTER(ALL('Component Type Nov - Jan'),'Component Type Nov - Jan'[Date] >= _lastmonthbegin && 'Component Type Nov - Jan'[Date] <= _lastmonthend)) stops it returning (Blank) and allows it to interact with the date slicer, but it does not respond to the Component Type slicer and returns a grand total of the value of all component types. If anyone could tell me why the Month Before Selected doesn't work in the same way as Selected Month despite having identical code, and how to fix this issue I would be very appreciative. Thanks.
Generate as a variable within a Measure vs Table Help
Hey all, Need help with a more efficient solution. I'm taking 'sales' and spreading them from the sales date by 12months. Meaning..the same sales number is spread evenly across 12months in order to setup a needed calculation. I'm currently doing this by generating a table and adding a column called 'Date' which provides all of the dates between the sales month and sales month +12. It works fine as a 'table'...but I was wondering if it would work faster as a nested variable within a measure...in order to lower the size of my model and just store it as a temp table within the measure and call out only what I need. Below is the generate code... GENERATE( 'biplanning Sales_Fcst', FILTER( CALENDAR(MIN('biplanning Sales_Fcst'[Accounting Month]),MAX('biplanning Sales_Fcst'[Deferred Date])) ,[Date] >= 'biplanning Sales_Fcst'[Accounting Month] && [Date] <= [Deferred Date] && DAY([Date])=1 ) ) From this table...I'm creating a calculated column to get the correct counts. If possible...I'd rather create a measure if this will create efficiencies. Let me know what you think. Much appreciated!Variable date range in variable
I have a number of pages in my Power BI report that display counts and sums of data from tables imported by a couple of different systems. I change the date range in the filters of each object to match the most recent week (used for meeting presentation). Before the meeting I go into each object and set the new date range. Since my pages combine various counts and sums for two different data sets, I can't simply set the date range on the page or the report. This seems like a great place to have a global variable for StartWeekDate and EndWeekDate. Change them once someplace and use the variable in my filters. I understand that this is not possible but haven't been creative enough to figure out how to do something similar. Is there a way in a measure using DAX to pull the start and end dates from a simple temp table where I can edit it each week?
