User Profile
talespin
Solution Sage
Joined 2 years ago
User Widgets
Contributions
Re: Running Total based on two values from different tables.
hi mgiusto If its 600 records, it shouldn't take that much time. There are other options. In power query, merge Orders and OnHand table on "org" and "code" and bring in OnHand total into Orders table. You can use calculated column --------------------------------------------- CalCol On Hand = VAR _Org = Orders[org] VAR _Code = Orders[code] VAR _OnHandQty = Orders[on_hand] VAR _OrdDate = Orders[Date] VAR _SumQty = CALCULATE( SUM(Orders[ord_qty]), REMOVEFILTERS(), Orders[org] = _Org && Orders[code] = _Code && Orders[Date] <= _OrdDate) RETURN _OnHandQty - _SumQty Or you can use a measure --------------------------------------------- On Hand = VAR _SelOrg = SELECTEDVALUE(Orders[org]) VAR _selCode = SELECTEDVALUE(Orders[code]) VAR _SelDate = SELECTEDVALUE(Orders[Date]) VAR _OnHandQty = CALCULATE( MAX(Orders[on_hand]), REMOVEFILTERS(), SUMMARIZE(Orders, Orders[org], Orders[code]) ) VAR _SumQty = CALCULATE( SUM(Orders[ord_qty]), REMOVEFILTERS(), SUMMARIZE(Orders, Orders[org], Orders[code]), Orders[Date] <= _SelDate) RETURN _OnHandQty - _SumQty If above solution does not work for you and you still want SQL. Select o.org, o.code, o.Order_Date, oh.on_hand_tot, (oh.on_hand_tot - SUM(o.ord_qty) OVER(PARTITION BY o.org, o.code ORDER BY o.Order_Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) "Runningtot" from Orders o inner join OnHand oh on o.org = oh.Org AND o.code = oh.code Please note that running total is calculated per group(org and code) and in orderdate ascending order, if you change sort order of these three column, running total will still be correct but result will look like bonkers 😄 if that is not the case please share pbix file with same data as you shared in your first post.Re: Determining the Completion Date Based on the Start Date + <xx> Working Days
hi Rosh88 I have a disconnected CALENDAR Table, please note that I have True/False(Boolean) in IsWorkingDay, you can change DAX to 'CALENDAR'[IsWorkingDay] = 1 instead of just 'CALENDAR'[IsWorkingDay] This is a calculated column CompletionDate = VAR _StartDate = 'Fact'[StartDate] VAR _WorkingDays = 'Fact'[WorkingDays] VAR _FltrTable = FILTER('CALENDAR', 'CALENDAR'[IsWorkingDay] && 'CALENDAR'[Date] >= _StartDate) VAR _WorkingDaysTbl = ADDCOLUMNS( _FltrTable, "@RANK", RANK(DENSE, _FltrTable, ORDERBY('CALENDAR'[Date])) ) RETURN MAXX( FILTER(_WorkingDaysTbl, [@RANK] = _WorkingDays), [Date])Re: Dynamically display currency for a measures based on slicer selection
hi DmitryAD7 These two sound more like a hypothetical scenario. You don't want to hardcode currency/country/formattting. It should be dynamic. Please check below solution if it works as per your requirement. If only two countries are selected Country 1 and Country 2 - currency format is "F #,#". If only two countries are selected Country 4 and Country 5 - currency format is "G #,#". Created duplicate table to yours and added currency symbol to table itself. Then created this measure. Value with Format = VAR _Selected = ALLSELECTED(Hierarchy2[Country]) VAR _CountSelected = CALCULATE(COUNT(Hierarchy2[Country]), ALLSELECTED(Hierarchy2[Country])) VAR _CountCountry = CALCULATE(COUNT(Hierarchy2[Country]), REMOVEFILTERS()) RETURN IF( _CountSelected = _CountCountry, FORMAT( SUM(Hierarchy2[Value]), "#,#"), IF( _CountSelected = 1, FORMAT( SUM(Hierarchy2[Value]), VAR _Currency = SELECTEDVALUE(Hierarchy2[Currency]) RETURN "\"&_Currency&" #,#" ), IF( _CountSelected = 2 && "Country 1" IN ALLSELECTED(Hierarchy2[Country]) && "Country 2" IN ALLSELECTED(Hierarchy2[Country]), FORMAT( SUM(Hierarchy2[Value]), "F #,#"), IF( _CountSelected = 2 && "Country 4" IN ALLSELECTED(Hierarchy2[Country]) && "Country 5" IN ALLSELECTED(Hierarchy2[Country]), FORMAT( SUM(Hierarchy2[Value]), "G #,#"), FORMAT( SUM(Hierarchy2[Value]), "#,#") ) ) ) ) Pbix file https://drive.google.com/file/d/1Io9Quo2uoI_kPnZWsYXJIcFjZk18ZULW/view?usp=sharingRe: The IN operator finds all values within ALLSELECTED, even though slicers have been selected.
hi AABright I am not sure if you need to be at square one, there might be better approach to doing what you want to do, since we do not have any detail on your requirement, so here is my try to address square one. You see Dim and Orders table linked on code(Integer field). Slicer contains code from Dim table and Table visual contains all attributes from Orders table. Create this measure --------------------------------------------------------------- IsSelected = VAR _SlicerValues = ALLSELECTED(Orders[code]) VAR _SelectedCode = SELECTEDVALUE(Orders[code]) RETURN IF( _SelectedCode IN _SlicerValues, "Selected", "Not Selected") ---------------------------------------------------------------Re: The IN operator finds all values within ALLSELECTED, even though slicers have been selected.
hi AABright "I am attempting to make a calculated column which creates a flag when a value in the table has been selected." This is not possible, Calculated colums are refreshed during report refresh and are not influenced by slicers. You need to create measure.
