LOOKUP-multiple-VALUES to perform aggregations on
I have a fact table containing player performance data of their matches in League of Legends. I want to be able to display the average stats of a specific player by their username/position and compare/relate those stats to their opponents and teammates. This specific data set only contains my own matches (with my own stats being the target of the analysis), but ideally, I'd find a way to find these stats in larger data sets also containing matches that don't include the subject I want to analyze. The way it works The players are put into random teams each match I created an unique teamId by merging MatchId and team-color There are five players per team and each has its own unique position/role/lane per team. Referred to as lane (Top, Jungle, Mid, Bot, Support) The players do hold on to their Username between games Referred to as Summonername I created an unique participantId by merging the matchId + team color + position Dataset references Attempt 1: avg enemy CS = CALCULATE( AVERAGEX( FILTER(Players, Players[Lane] == "Mid"), [Total.MinionsKilled]), ALL(Players), FILTER(Players, Players[summonerName] <> "Qyntius") ) This only works if the dataset is limited to matches that have "Qyntius" present in the match and playing Mid specifically. Unfortunately, there are also matches where this is not the case. This means I'd have to filter the rows of the entire dataset to the matches played in a single lane, but I also want to be able to compare the players performance on different lanes with each other. Now that I'm typing this, I should create a table for each lane the target played? But that seems messy with relationships right? Attempt 2: Rival Gold = VAR team = LOOKUPVALUE ( Players[TeamId], [summonerName], "Qyntius" ) VAR lane = LOOKUPVALUE ( Players[Lane], [summonerName], "Qyntius" ) VAR match = LOOKUPVALUE ( Players[match_id], [Summonername], "Qyntius" ) RETURN CALCULATE ( AVERAGE ( Players[Total.Gold] ), FILTER ( players, Players[match_id] = match ), FILTER ( players, Players[Lane] = lane ), FILTER ( players, Players[TeamId] <> team ) ) I think this is what I want, but LOOKUPVALUE can only return a single value. I want LOOKUPVALUE to "build" a new table of direct rivals (same position, same match, different team), if that makes sense. It seems like something of a loop is needed to achieve this. I am also looking into creating a calculated column to "tag" all players with either "ally", "enemy", or "nomatch" in relation to the player I want to analyze, but running into the same issues. Worst case scenario I can always try to add this column at the data sourcing process as the table is built match by match, but for learning's sake, I'm trying to find a way to do it in Power BI. I apologize if this question is silly and for the wall of text, but I am stumped in trying to figure this out by myself. This is my first project. I downloaded PowerBI a little over two weeks ago and I have no experience in DA/coding so bear with me.looping through past dates
Hellou guys, I have problem with example below. I am in some DAX course with exercizes , but even with this result I cant get red highlighted part. Task was, identify and count new customers in specific day(means, they had purchase first time that date). Cant get red highlighted part. According my backround from excel I suppose that there is loop via customer key- then inside that is comparing (another loop, but via dates) if ANY of their purchases was before current date of visual(in this case for example first line-1/5/2011.. So their are two loops basicaly..Is that correct thinking, pls?And if so, what exactly is triggering those loops? SOLUTION:
How to convert weekly dates to fortnightly dates
Hi Everyone, Its my first post in the forum so apologies if the post is posted incorrectly. I want to convert weekly dates to fortnightly dates. While this sounds simple - I cant seem to get it working. I know there was a similar post: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Grouping-dates-into-fortnights/m-p/949837 but this didnt quite work for me as the numbers didn't while line up. This is the data I have which goes up to 2023 (see below). I essentially want the 'Fortnight' column to show 06/01/21 for the first period. Then from 7/01/21 - 20/01/21 to show as 20/01/21 and 03/02/21 for the next 2 week period and so on. I intially tried "Fortnight = VAR __BaseDate = DATE(2021,1,6) VAR __2w = (__BaseDate) +14 RETURN if(Dates[Date]<=__BaseDate,__BaseDate,__2w)" but ovbiously this only works for the first period. wondering if some sort of loop could be made using this? or another simpler method altogether? Any help would be greatly appreciated! Kind regards, Mike
Loop through two columns to get max result entries
I'm in need of a solution, which I'm sure is possible, but beyond my current PowerBI skill-set. Thanks in advance for your help. I have a table with 4 columns: [Row ID] - a generic row# of the table [Entry No.] - link to an entry in an inventory table [Entry No. (IN)] - if the [Entry No.] was an inbound entry (an increase to inventory) then identifies the same as [Entry No.] [Entry No. (OUT)] - if the entry was an outbound entry (an decrese to inventory) then identifies the same as [Entry No.] The purpose of the table is to trace the moment of inventory transactions through the system - from the initial inbound/increase entry through to whe the inventory left/decreased. Below are some examples of increasing complexity: Example 1 [Entry No.] 1 is an increase in inventory of 50 [Entry No.] 2 is a decrease in inventory of 50 linked to [Entry No.] 1 Required result I need is Example 2 - slightly more complex same as example 1, but this time two entries [Entry No.] 2 and 3 have 25 each consuming the original 50 Required result I need is Example 3 - slightly more complex again same as example 1 and 2 except this time [Entry No.] 2, of which 25 came from [Entry No.] 1 gets consumed again into [Entry No.] 4 and 5 at a quantity of 10 and 15 respectively. [Entry No.] 3 also goes on to be consumed into [Entry No.] 6 Required result I need is Example 4 - another slightly more complex scenario 25 of [Entry No.] 1 goes into [Entry No.] 2 which in-tun goes into [Entry No.] 3 which in-turn goes into [Entry No.] 4 which finally gets split into [Entry No.] 5 and 6. Note 25 of [Entry No.] 1 is still yet to be used, so this needs to show also. Required result I need is Hopefully from these examples below you can see the table I'm trying to build could be described as: "for each inbound entry (defined as where [Entry No. (IN)] not blank AND [Entry No. (OUT)] = 0) I need to know where and in what quantity it has ended up." Where it ended up will always be the MAX([Entry No. (OUT)]). However it's not a rule that the [Entry No. (IN)] is always less than the [Entry No. (OUT)], sometimes (IN) will be higher than (OUT), however the MAX([Entry No. (OUT)] will always be the "where it went" for the higher (IN). example: Required result I need is You'll see fomr the examples also that any quantities yet to be fully used/consumed should also be visible. below is a larger set of example data and expected results. Row ID Entry No. Entry No. (IN) Entry No. (OUT) Quantity 1 1 1 0 50 2 2 1 2 50 3 3 3 0 50 4 4 3 4 25 5 5 3 5 25 6 6 4 6 13 7 7 4 7 12 8 8 8 0 50 9 9 8 9 30 10 10 9 10 15 11 11 9 11 15 12 12 10 12 7 13 13 10 13 8 14 14 11 14 10 15 15 15 0 50 16 16 15 16 10 17 17 15 17 10 18 18 15 18 10 19 19 16 19 5 20 20 17 20 7Using DAX Loops - Can it work for this?
I'm trying to create a table visual (as below), where I can specify the starting point, and then use the output from the previous month to calculate the output for the next, and so on. I'll try to explain the best way I can. I'm staring with the value for 1081 for 12/31/2019 (that is a static value). I then want to take that value and use it in a calculation for the next month(s) output. Example: Jan2020: 1081+341-453 = 969 Feb2020: 969+352-271=1050... and so on I started with the following DAX, but this obviously won't work throughout (so I stuck a temporary blank in there for result after Jan). I'm assuming I need a loop so I can store the previous output in a variable to use in the next month's calculation. I am pretty good with DAX, but not familiar enough to understand the looping mechanism needed for something like this. Any direction would be greatly appreciated. Thanks, Laurie
DAX - Consecutive 2 sale difference in a year for every customer
Scenerio: Recovered customer in a year - for a selected period, there is a time gap between 2 sales which is equal or greater than “Nr of Months before Lost” No of months before lost (slicer of months) : 1, 3, 6, 12 I have a table have different customers and different sales date staring from 1 jan 2019 to 25 sep 2019 Ex: A customer has sales staring from jan 2019 then in feb then in june and then in aug Want to calculate difference between every 2 sale dates for every customer in 2019... Trying to implement loop functionailty: No of customers = Var FirstBuyInperiod = min (Sales[SALES_RECOGNITION_DATE]) Var NextSale = min(Sales[SALES_RECOGNITION_DATE]) > FirstBuyInperiod Var looptable = GENERATESERIES(FirstBuyInperiod,max(Sales[SALES_RECOGNITION_DATE])) Var looptable1 = ADDCOLUMNS(looptable, "datediff", Datediff(Firstbuyinperiod , NextSale , Month)) //here want to update the firstbuyinperiod and nextsale for 2nd iteration set FirstBuyInPeriod = NextSale set NextSale = min(Sales[SALES_RECOGNITION_DATE]) > FirstBuyInperiod RETURN COUNTROWS(CALCULATETABLE(VALUES(DimCustomer[CUSTOMER_NUMBER]), FILTER(looptable1, [datediff]>= [No of month before lost slicer]))) Please suggestCumulative upto in Dax
Hello, I hope my query doesn't sound funny but I am trying to create a progress chart for the car usage per sequence ( trip ). Here is an example of how the table looks like and the last column is the result I am trying to generate using Dax. Sequence Car type Hours/Sequence 1 Toyota 5 2 Toyota 9 3 Toyota 1 4 Toyota 3 1 Ford 19 2 Ford 7 3 Ford 4 4 Ford 6 1 GMC 10 2 GMC 1 3 GMC 16 4 GMC 5 1 Nissan 7 2 Nissan 0 3 Nissan 3 4 Nissan 1 And the additional generated column using DAX should look like this. Cumulative Hours upto this sequence 5 14 15 18 19 26 30 36 10 11 27 32 7 7 10 11 Do you think its possible to create a Dax loop to distinguish between different car types and calculate the sum of hours up to each sequence.
