SELECTEDVALUE measure from slicer keeps returning blank value when used on another measure
So I have this simple dropdown slicer which is a list containing only a couple of dates, which in turn comes from a single-column table currently with NO relationship to either the date and the fact tables (CalData and Contas_a_Pagar respectively) in the semantic model: Then I created the following measure that picks up whatever choice I made on the aforementioned slicer: Data-base = SELECTEDVALUE(Data_Base_Lista[Data-base]) And when I drop this measure on a card visual it works just fine, so the measure is in fact picking up my date slicer choice: PROBLEM: However, when I define and evaluate the same measure on DAX query view, it returns BLANK: ↓↓↓↓↓↓↓ The problem is that I'm using this measure as a base date to calculate other measures, for instance to calculate the total of invoices whose due dates are on or after the selected base-date, and this measure is instead returning the grand total of all invoices (it should be $1,535,135.96 not $5,475,597.89 as shown below), and the reason is because the measure 'Date-base' is BLANK, so this other measure ends up picking up all invoices since their due dates are all on or after a blank date: Conversely, when I setup another measure that is supposed to calculate the total of invoices whose due dates are before the selected date, this measure also returns BLANK, instead of the correct total amount of $3,940,461.93, since this measure can't find any due date that is prior to a blank base-date: QUESTION: I know this is a basic stupid issue, but I just can't seem to figure out a way to fix this, even after having watched several videos about the SELECTEDVALUE function. What am I doing wrong here? Thanks, Leonardo
How Can I Calculate the Years Service of each employee
I have Table EmploymentDetails I will mention the importent columns EmploymentStartDate , PersonnalNumber I want to calculate the Years of Service and the number of months in Years of Service as You shown above I tired to create column to calculate Years of Service " Years of Service = VAR Years = DATEDIFF(EmploymentDetails[EmploymentStartDate],TODAY(), YEAR) VAR Months = DATEDIFF(EmploymentDetails[EmploymentStartDate],TODAY() , MONTH) - (Years * 12) VAR result = Years + (Months / 12) RETURN ROUND(result, 1) " this Measure worked correctly it has calculated the Years of Service from EmploymentStartDate of each employee untily today but I want to calculate the Years of Service depend on the slicer the employee's years of service are calculated based on the date I tried to create this Column " YearsOfServices = VAR Years = DATEDIFF(EmploymentDetails[EmploymentStartDate], SELECTEDVALUE(EmploymentDetails[EmploymentStartDate]), YEAR) VAR Months = DATEDIFF(EmploymentDetails[EmploymentStartDate], SELECTEDVALUE(EmploymentDetails[EmploymentStartDate]), MONTH) - (Years * 12) VAR result = Years + (Months / 12) RETURN ROUND(result, 1) " but there is not result could anyone help me to create a column and Measure please ? thank in advance
YTD filter based on the selected month
Hello, I'm trying to create a dynamic time filter based on the selected month (Always one month selected in the report): MTD: Selected month YTD: Janv - to selected month (No cumulation !) FY: Janv - Dec First I tried with calculation group but I always get cumulated YTD result. Now I try with a DAX table but I can't link the selected month in the DAX table... Model: DAX Filter table: MonthFilter = Var _SelectedMonth= max(SelectedMonth[SelectedMonth]) Var _MonthFilter = UNION( ADDCOLUMNS( SELECTCOLUMNS(GENERATESERIES ( _SelectedMonth,_SelectedMonth,1 ) ,"Mois",INT([Value])) ,"MonthFilter","MTD" ,"Order",1) ,ADDCOLUMNS( SELECTCOLUMNS(GENERATESERIES ( 1,_SelectedMonth,1 ) ,"Mois",INT([Value])) ,"MonthFilter","YTD" ,"Order",2) ,ADDCOLUMNS( SELECTCOLUMNS(GENERATESERIES ( 1, 12 ,1 ) ,"Mois",INT([Value])) ,"MonthFilter","Full Year" ,"Order",3) ) Return _MonthFilter Result: Thanks for your help 🙂
Filter a table based on multiple selected values
Hi all, I am currently using a switch() statement within a measure to filter a table appropriately depending on a selection like so: _Orders_BY_superhero_preference = VAR SelectedOption = SELECTEDVALUE(SuperHeroes[name]) VAR code = SELECTEDVALUE(SuperHeroes[code]) VAR all_codes = SELECTCOLUMNS(SuperHeroes,"code",[code]) RETURN CALCULATE([_Orders],FILTER(OrderTable, OrderTable[matched] = "Y" && OrderTable[order_number] IN all_codes && SWITCH( SelectedOption, "Batman", RELATED('CustomerAttributes'[Fav_superHero]) = "Batman" && CONTAINSSTRING(OrderTable[order_number],code), "Spiderman", RELATED('CustomerAttributes'[Fav_superHero]) = "Spiderman" && CONTAINSSTRING(OrderTable[order_number],code), "Superman", RELATED('CustomerAttributes'[Fav_superHero]) = "Superman" && CONTAINSSTRING(OrderTable[order_number],code) ))) Where the measure [_Orders] is basically a DISTINCTCOUNT of order ids, and the rows included in the aggregation are determined by which superhero name is selected. This works with a single option eg: name = Batman. However, is there a way that I can modify this so that it works with multiple selected options? In other words if Both Batman and Spiderman are selected, the OrderTable is filtered such that it contains rows where the 'matched' column = "Y", the order_number value exists in the SuperHeroes code table, and only rows where the related values in the customerAttributes table are Batman or Spiderman are included in the aggregation? I've tried replacing SELECTEDVALUE with VALUES, but having some trouble with figuring out how to account for multiple selections within the switch logic.How to swich column based on the calue of SELECTEDVALUE?
Hi all; I am stacked in getting the value of SELECTEDVALUE in calculated column. The DAX formula I am using is returning blank while the same formula works and returns value when used with a measure. The DAX formula I am using looks like following: Legend = SWITCH( SELECTEDVALUE(Metrics[Metrics Fields]), "'MyMeasures'[% 0-5 months]",[% 0-5 months category], "'MyMeasures'[% 6-23 months]",[% 6-23 months category] ) thanksHow to use DAX formula SELECTEDVALUE with Field Parameters
Dear Power BI community, I would like to ask for your help as I am trying to use DAX formula SELECTEDVALUE based on field parameters, however it is not doable based on the following error message: IF user selects between two value the (Amount USD & Amount LCL) the calculation should be changing based on that: DAX formula : Field Parameter: Visualization selection for Amount USD & Amount LCL: Would it be possible for someone to help how to change the DAX formula SELECTEDVALUE part to make it work? Thank you for your advice in advance. Wishing you a wonderful date. Adam
