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!
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 =
Handling tables in memory
Hi, I am trying to get a new column for a table counting how many times each value appears on another table. Please see diagram below: With normal tables, this is relatively easy, eg. Expected result = ADDCOLUMNS( table1, "found", CALCULATE( COUNTROWS(table2), FILTER(table2, table1[Value] = table2[Value]) ) ) The problem is that I've written a rather large and complicated code that relies on this same operation, but where both tables are actually variables (e.g. table1 = {1,2,3,4,5,6,7,8,9}) instead of normal tables, and of couse, you can't do table1[Value] in these cases. I've tried countless approaches, and I can't get it to work. Any ideas? Thanks in advance.
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?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.
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-6045bdbf86b4Rating 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
