Get Previous year from selected year
Hello, Need help please ! I need to get value of previous year depend year select with a slicer. When I select 2021 I want to see value for 2020. how can I do that ? I try to use SELECTEDVALUE : CALCULATE(sum(value), YEAR(date)=SELECTEDVALUE(Col1)-1)... Thank You
Using SELECTEDVALUE slicer and returning all results if nothing is selected
Hello, I am using a slicer for my report legend and need to present all category options even if the data does not (yet) contain that scenario. Here is an example of the slicer I am trying to populate. Since we cannot color code slicer buttons (disappointing, please up vote my idea) you can see my current approach is dependent on all categories being included. I am currently attempting to use a disconnected category table and SELECTEDVALUE with my measures. I get the desired results if I make a selection. However, I need to return all unfiltered results if no selection is made. I found this article that described how to get all selected values from a slicer. Is it possible to do something similar and define <alternateResult> as if all options were selected?... SELECTEDVALUE(<columnName>[, <alternateResult>]) Is there a better approach entirely to achieve my desired static slicer? Below is a sample of my DAX. Thank you, Joe 'SelectedValue' is my unconnected table. Total Hours = VAR selectedValue = SELECTEDVALUE( SelectedValue[Category Value] ) RETURN CALCULATE( [Hours] , FILTER( ALL( 'Time Detail' ) , 'Time Detail'[Category Value] = selectedValue ) )Calculate Region revenue in a Country table
Hi I require the Dax to be able to calculate the region revenue instead of the country revenue in a matrix table by data set is as follows the table i'm trying to create in power BI is something like this where i display both the country Rev and the Region rev next to each other which i have managed to do with the following dax. Region Rev = CALCULATE(SUM('Dataset 1'[Revenue]),FILTER(FILTER(ALL('Dataset 1'),'Dataset 1'[Region]=SELECTEDVALUE('Dataset 1'[Region])),'Dataset 1'[Year]=SELECTEDVALUE('Dataset 1'[Year]))) However i cannot get it to calculate by industry and it is just giving me the total rev for the region, rather then by Industry by for that region. in theory all i want to do is stop the measure from filtering on Country and instead filter on the region for that country when it is displaying any values any help would be much appreciated!show last value on or before selected date
Hi I have a table that contains daily balances for several accounts by date. It doesn't have an entry for every date. The user selects a date they are interested in, I would like to display each account by value on a Clutered bar chart. I am trying to create a measure that will calculate a value. If the an entry doesn't exist for the selected date I would like the value of last entry before the selected date. This is what I have so far but is giving me the error max has been used in a true/false expression that is used as a table filter LastValueAmmount = CALCULATE( [Total Value], FILTER( ALL('Statement Date'[Date]), LASTNONBLANK('Statement Date'[Date],[Total Value]) ) , 'Statement Date'[Date] <= MAX ( 'Statement Date'[Date] ) ) [Total Value] is a measure I have created and just totals the closing balance field. here is my model thank you for all help
Help needed - How to create a single row table with the value from a selectedvalue.
I need to create a table and then with this table, to create another table. But the first table need to be created from a selection. Tried everything and I just can't go on. The measure getting the selectedvalue: Selected = SELECTEDVALUE(dProducts[ProductCode]) The first table attempt : FilteredBySelectedValue = Var _ProductCode = [Selected] Return ROW( "ProductCode",_ProductCode) The second table attempt : fBasketRelated = FILTER( CROSSJOIN( VALUES(FilteredBySelectedValue[ProductCode]),SELECTCOLUMNS( VALUES( fSalesDetails[ProductCode]),"RProductCode",[ProductCode])),[ProductCode]>[RProductCode]) The result : Nowhere. The model : stevedep The PBI file
SELECTEDVALUE in calculation not working
Hi I have a filter this filter: and I'm trying to do a calculation depending the selected Item Type Calculation no 1 is working fine and gives me the correct result, but no 2 and 3 are not working they return blank value. 1. CALCULATE(SUM(FT_StockTransactionsDetailed[Kg]),Dim_Items[Item Type]="B-Grade") 2. CALCULATE(SUM(FT_StockTransactionsDetailed[Kg]),Dim_Items[Item Type]="Waste") 3. CALCULATE(SUM(FT_StockTransactionsDetailed[Kg]),Dim_Items[Item Type]=SELECTEDVALUE(Dim_Items[Item Type])) Can anybody give me a hint? BR Lotte
Check if any value in each row appears in SELECTEDVALUE row
Hello, all. I had a student ask whether it was possible to filter a table visual for all rows that match any value in the row selected in another table visual. I said yes! and threw together this example (new link), but this requires the column names to be specified in the expression, like below: Shared Value? = IF( MIN('Customers Copy'[City]) = SELECTEDVALUE(Customers[City]) || MIN('Customers Copy'[State]) = SELECTEDVALUE(Customers[State]), // etc etc 1, 0) I'm wondering if it's feasible to do this dynamically, so that all columns of a table could be checked against all values associated with SELECTEDVALUE. Some sort of VALUES(ROW(...)) IN SELECTEDROW(...) if you will. I know DAX doesn't support traditional loop structures, but I suppose this is a bigger question about dynamically referring to columns in DAX tables (if only we had some sort of equivalent to Expression.Evaluate !!) .pbix file is linked above. Please note the calculated Customers Copy table (I'm also wondering if this is feasible without the copy table) and Shared Value? measure (used in a filter on the second table). Thanks for any ideas!Combined SWITCH and SELECTEDVALUE function not working for sum of multiple measures
Hi all, I'm trying to set up a DAX-based table visual for a financial statement - based on financial transactions. My current set up is now based on a YouTube guide (https://www.youtube.com/watch?v=J4317R5BvsA&t=539s). The major difference between my approach and the one in the YouTube guide is that I'm working on financial transactions rather than aggrecated categories. So far so good, but I'm running into trouble when trying to get values from a measure that sums other measures e.g. a gross margin, which shows a blank. My measure for retrieving and lining up the correct line item values looks like this: The measures that do work, are all written like this: The measures that don't work, are all written like this: I'm not a super user of Power BI at all, but I suspect that the "sum measures" shows a blank due to the fact that my "sum measures" contain multiple measures. I tried replacing the DAX code with a simple hard typed numbers (e.g. 1000-500-250) formula, which does show up in my table. Any ideas for a workaround or a totally different approach is much appreciated. Happy holidays! 😎 Best regards, Chris
