using field parameter inside a measure
Hi there, It is possible to use a field parameter inside a measure? I have a report where I display in a line chart different moving averages for one specific column (DJM). Each line is drawn using a measure (5 in total), and the users can select which line(s) to show using a slicer. I also allow the users to specify how many periods they want to use for a moving average. To do so I used a numeric parameter that I include in one of the measures (SMA #) like this: SMA (# weeks) = AVERAGEX( DATESBETWEEN(Carro[Date],MAX(Carro[Date]) - (prmMA[prmMA_value]-1)*7, MAX(Carro[Date])), CALCULATE(SUM(Carro[DJM])) ) This report works perfectly for just one data column (DJM), but if I want display the same line chart for a different data column I must create another 5 measures for that new/different data column. The problem is that I have around 20 columns and I do not wish to create 100 measures... I tried to use the field parameter the same way I have used the numeric parameter, trying to replace in the measures Carro[DJM] for prmField[prmField Opt], but if I click to display this line/measure, the chart shows a display error (SUM does not admit strings). SMA (5 weeks) = AVERAGEX( DATESBETWEEN(Carro[Date],MAX(Carro[Date]) - (5-1)*7, MAX(Carro[Date])), CALCULATE(SUM(prmField[prmField Opt])) ) PS: I could no tfind how to upload here my pbix file. Please use this link or check the pictures below: https://we.tl/t-mFnePxOlwp Thanks a lot for any help you might provide!
Generateseries is not working with a parameter and single value slicer
I am trying to create a single value slicer I have created a parameter "Target Parameter" and a table "Target" My parameter is "Target Parameter = GENERATESERIES(MIN(Target[Target]),MAX(Target[Target]))" (I have also tried adding the increment of 1 with the same result) the Target Table has 1 column with numbers from 0 to 1048576 generated from a linked excel file. Data type is Decimal Number but I have tried Whole and Fixed as well. When I look at the tables, I don't see any error in the sequenced numbers. When I enter a value such as 100000 into the associated slicer, the number in the slicer automatically changes to 99516 When I enter 2 it is automatically changed to 1 When I enter 1048570 is automatically changed to 1048576 Every number I have tried is changed to a different value except the largest number 1048576 Is there anyway to get it to accept and use the value entered? I intend to use the entered number in other calclations.
Issue With Parameter and Filtering
Hello! I seem to be stuck in a catch-22 with using a parameter for subreporting. On one hand, I want to connect the parameter to the relationship model, and on the other measures aren't calculating correctly when I do so. Allow me to first explain what I'm looking to do, and then introduce the problems I'm experiencing. Users of the report first select a semester and student type(s). Data regarding this initial point in time is displayed, such as entry major code. Depending on what semester is picked, flag measures named included_low and included_high create a window for which semesters are relevant for these students (i.e. Spring 2010 has included_low = 0 if the user chose Spring 2011). Also present is a subsection where users can select a future semester for a breakdown of those students at that time. The slicer controlling this function is driven by the parameter, which is a list of term codes. When creating the parameter, I originally tried to use included_low and included_high within the GENERATESERIES code that populates it. I learned that parameters are created at the table level, and thus the parameter cannot be dynamically produced. Why Add it to the Relationship Model? Since the parameter can't generate on-the-fly, I filter the slicer housing it with the previously mentioned flags. Without a relationship between the parameter and the model, I'm not able to get the flags to work as filters on the visual. Also, creating calculated columns on the parameter to replicate the same logic seems stuck at the table level too - I tried making a flag which peeks into the chosen values, but with no success. I have connected the parameter to the relationship model, with a single direction. The parameter shouldn't be able to impact the model, but the model should be able to impact the parameter. Doing so causes the flags to work, and I no longer display nonsense terms in the slicer! Why NOT Add it? Despite the direction outlined above, measures are calculating unexpectedly which I need to display in the subreport section when the parameter is included in the relationship model. For example, Total Graduates counts how many students have graduated in or before the selected semester of the parameter. Suppose 1 student graduated in Fall 2017, Fall 2018, and Fall 2019. If you select Fall 2018, you'd expect to see there were 2 total graduates. Instead, it says 1 person has graduated. Surprisingly, displaying tables of graduation records (ID and semester) are NOT having that problem (I've triple checked that the filter is applied to the table), and degrees earned before the selected semester are shown. The code for Total Graduates is as follows: Total Graduates = VAR tg = COUNTROWS(CALCULATETABLE(DISTINCT(graduation[ID]), FILTER(graduation, graduation[ACADEMIC_PERIOD_GRADUATION] <= 'Select a Term'[Selected Term Value]))) RETURN IF(tg, tg, 0) Additionally, I've tried to add "ALLEXCEPT(enrollment, enrollment[ACADEMIC_PERIOD])," as another filter. This is the field from the relationship model which connects to the parameter. Can anyone explain this mysterious behavior, or know how I can get the best of both worlds? Thanks for your help!Different column output basing on Parameter Selection
Hi Guys and Welcome Everyone! my first post in this community :). I have following query. I have created parameter using "New Parameter" -> "Numeric Range" Then I have edited parameter settings as follows: Then I would like to have column with different output depending on parameter selection. I wrote formula as follows: When I test it in report view, I always get "NOTPASSED", whatever I choose in the slicer. Do I miss anything or is there other workaround to achieve my goal?
Changing Data through slicer
I would like to create new data based on a slicer setting i have chosen linked to a parameter values. For example, i have a baseline monthly forecast for 2022. I would like to apply a percentage change to this baseline forecast for alternative scenarios. For instance, i want to see what the forecast looks like if it was 5% higher than the baseline forecast. I need this at the data column level and not a measure so that i can create some formulas off that new column data. For instance, if column B is the baseline forecast, I have create a new column that equals the baseline forecast column multiplied by (1+ parameter value); in this case, i set my slicer to 5%. I could have easily chosen 6% or something else, that's the type of functionality I'd like to build. I have created a measure that works visually for this, but i realized I really need this at the column level to use it for other things the way I want. Adjusted Forecast = 'sheet1'[Baseline Forecast]*(1+'Adjustment Parameter'[Adjustment Parameter Value]/100)
Parameter / LookupValue / Slicer
HELP! What I would like is a slicer to control a parameter. That will be combined with a column that results in a lookupvalue from another table. Any ideas? FYI, I am a rookie that is learning on the fly. TABLE is 'CUCDataOH-OR-TR' PARAMETER is Priority IDTR = Priority[Priority Value]&" - "&'CUCDataOH-OR-TR'[Whse] My "Priority" slicer works fine, but doesn't update in the table above If it works properly it will lead to this blow TransferToWhse = LOOKUPVALUE(TrPriorityShip[To Whse],TrPriorityShip[IDTR],'CUCDataOH-OR-TR'[IDTR]) amitchandak
How to Calculate Total after using Parameters
Hi all, I got below report and cannot calculate a correct grand total. Some logic for calculation - Density is anumeric parameter - Each Warehouse has own Density Value - CuFt = SqFt * Density I am using below DAX for calculating CuFt for each warehouse, but I cannot get the correct total (just place an text for remind). Can anyone help and any other solution? DAX Code Capacity (CuFt) = VAR GSW=if(SELECTEDVALUE(Warehouse[Warehouse])="GSW",'WH Capacity Measurement'[WH CuFt Capacity]*'Density - GSW'[Density - GSW Value],0) VAR Oneill=if(SELECTEDVALUE(Warehouse[Warehouse])="Oneill GA",'WH Capacity Measurement'[WH CuFt Capacity]*'Density - ONLGA2'[Density - ONLGA2 Value],0) VAR EOSCA=if(SELECTEDVALUE(Warehouse[Warehouse])="EOSCA",'WH Capacity Measurement'[WH CuFt Capacity]*'Density - EOSCA'[Density - EOSCA Value],0) VAR EOSTX=if(SELECTEDVALUE(Warehouse[Warehouse])="EOSTX",'WH Capacity Measurement'[WH CuFt Capacity]*'Density - EOSTX'[Density - EOSTX Value],0) VAR Irvine=if(SELECTEDVALUE(Warehouse[Warehouse])="Irvine",'WH Capacity Measurement'[WH CuFt Capacity]*'Density - Irvine'[Density - Irvine Value],0) Return if(SELECTEDVALUE(Warehouse[Warehouse])="GSW",GSW, if(SELECTEDVALUE(Warehouse[Warehouse])="Oneill GA",Oneill, if(SELECTEDVALUE(Warehouse[Warehouse])="EOSCA",EOSCA, if(SELECTEDVALUE(Warehouse[Warehouse])="EOSTX",EOSTX, if(SELECTEDVALUE(Warehouse[Warehouse])="Irvine",Irvine, "Depend Density")))))Create a Parameter off of a Column Query
I'm trying to create a parameter using a Query from the values in my dataset. From all of the tutorials I've watched, creating a Parameter requires you to have static values (i.e. a series of %'s, dates, metrics, etc.), however I'd like to have my parameter values be based off of the values in a column of my data (these names will change every time I refresh the data). Is this possible to do? For Example, I have a dataset with a column named "Baseline Scenario" with a series of scenario names listed below. However, when I upload a new dataset, or refresh the data, these Scenario Names may change and will no longer match what is listed, there may be more, less, etc. What I'd like to do is have the Parameter Table read from whatever is listed here so that every time I refresh the data, I can have a slicer automatically know the scenario names based off of what was included in the data. Is this possible?Using a Value in Field Parameter in a Dax Command
I'm having a trouble to bring a value from a Field Paramenter to a Dax Command. I want to count the number of "Acct Number" by Geo and want to get the count difference between the selected "scenario" Column. Currently, Scenario 1,2,3 are set up as Field Parameter so that based on the selection in a slicer, the Account Count broken out by Black and White changes. The problamatic part is, I want to calculate the count difference between the selected scenario (dynamically change) and the original value (which is based on "Type" column). Since it is referencing a different column, I've tried with a selectedvalue function but it never works... Any ideas on how to write DAX queries for the yellow highlighted parts?
