Problemns with IF logic VAR
Hello everyone, who are you guys doing? I am preparing a comparative dashboard about two dev platforms and encountered a logical problem. Basically, I have to apply a conditional logic within DAX which I thought would be easy. Every time I filter a value (visual filter) on the page, it will perform a calculation by taking fixed values and multiplying them by the value selected in the filter. So, hypothetically speaking, if my Project A does not have available data, I have to perform this calculation for it: (Fixed Jenkins Median / Fixed Azure Median) * Filtered project median value. The measure was implemented as follows: if the calculation needs total values from both platforms, and when I filter by project, I created a VAR for the total of Azure and Jenkins but FIXED, so I can derive the values by division. VAR for acronyms and VAR totals were also created to perform the other calculations. Has anyone done something similar and can help me? Here is the DAX with logic: // CONDITIONAL VAR JenkinsResult = IF( ISFILTERED(dGeneral[Acronym 2]), IF( ISBLANK(FilteredJenkinsMedian), (FixedJenkinsTotalMedian / FixedAzureTotalMedian) * AzureAcronymMedian ), TotalJenkinsMedian ) VAR Platform = SELECTEDVALUE(dGeneral[Platform]) // SWITCH VAR FinalResult = SWITCH( Platform, "Azure", TotalAzureMedian, "Jenkins", JenkinsResult ) RETURN FinalResult
Perform the equivalent of Excel's SUMPRODUCT on an average * Column , DIVIDED by a other Column
Hello, I have been days trying to solve this issue and I really seem to be at my wit's end, maybe because I'm still too noob. I have this tables and columns of data: Table1: COLUMN_A = Decimal Number COLUMN_B= String, can be two categories, A or B DATEKEY =Date Table2: datetable (with all the usual date table stuff, plus two extra collumns): Usual date table stuff: each row is a day of the year. Date= is the datekey of the datetable, for example 12/02/2020, Year = example, 2020 if datekey is 12/02/2020 Mes = Month of datekey in integer, january = 1, for example, for 12/02/2020 it would be 2. ETC (doesn't matter) Not so usual datetable stuff: -Number_of_days_of_month = Integer, number of days of that month for that row (each row is a day in a year, for example, if datekey is12/02/2020 ;Number_of_days_of_month would have the number 29, as 29 days) -SUMDaynumsofmonth = Integer, agregation of all the total days of all the months that have ocurred up to that point for that row (each row, as said , is a day in a year, so for example if datekey is 12/02/2020, SUMDaynumsofmonth would have the value of 60, because january had 31 days, and february has 29 days, 31 + 29 = 60, this column always gives the sumation of all days of all the months up to that point, NOT the days of the year up to that point, other examples: for datekey 27/04/2020 it would return 31(january) + 29(february) + 31(march) + 30(april) = 121, all rows with datekey of the month of april would return 121 , datekey 29/02/2020 would return 60 again, all the rows in datekey of the month of february would return 60, essentially, it only cares about the month of the datekey, when performing the calculation, not the day. For this column a dax formula was used (that works fine from what I can see, I will share it's calculation nevertheless for completeness of the question) : SUMDaynumsofmonths = //first we calculate the number of days of each month// VAR days_january = DAY(EOMONTH(DATE( YEAR(datetable[Date]),1,1), 0)) VAR days_february = DAY(EOMONTH(DATE( YEAR(datetable[Date]),2,1), 0)) VAR days_march = DAY(EOMONTH(DATE( YEAR(datetable[Date]),3,1), 0)) VAR days_april = DAY(EOMONTH(DATE( YEAR(datetable[Date]),4,1), 0)) VAR days_may = DAY(EOMONTH(DATE( YEAR(datetable[Date]),5,1), 0)) VAR days_june = DAY(EOMONTH(DATE( YEAR(datetable[Date]),6,1), 0)) VAR days_july = DAY(EOMONTH(DATE( YEAR(datetable[Date]),7,1), 0)) VAR days_august = DAY(EOMONTH(DATE( YEAR(datetable[Date]),8,1), 0)) VAR days_september = DAY(EOMONTH(DATE( YEAR(datetable[Date]),9,1), 0)) VAR days_october = DAY(EOMONTH(DATE( YEAR(datetable[Date]),10,1), 0)) VAR days_november = DAY(EOMONTH(DATE( YEAR(datetable[Date]),11,1), 0)) VAR days_december = DAY(EOMONTH(DATE( YEAR(datetable[Date]),12,1), 0)) RETURN //now, depending of the month of the datekey, defined by column mes, we will add the corresponding variables to include the total days of only the months up to the one we are receiving on mes column // IF(datetable[Mes] = 1 ,days_january , IF (datetable[Mes] = 2 , (days_january + days_february) , IF (datetable[Mes] = 3 , (days_january + days_february + days_march) , IF (datetable[Mes] = 4 , (days_january + days_february + days_march + days_april), IF (datetable[Mes] = 5 , (days_january + days_february + days_march + days_april + days_may) , IF (datetable[Mes] = 6 , (days_january + days_february + days_march + days_april + days_may + days_june), IF (datetable[Mes] = 7 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july), IF (datetable[Mes] = 8 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august), IF (datetable[Mes] = 9 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august + days_september), IF (datetable[Mes] = 10 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august + days_september + days_october), IF (datetable[Mes] = 11 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august + days_september + days_october + days_november), IF (datetable[Mes] = 12 , (days_january + days_february + days_march + days_april + days_may + days_june + days_july + days_august + days_september + days_october + days_november + days_december)) ) ) ) ) ) ) ) ) ) ) ) I repeat, this column works fine for the moment, the problem comes later Metrics to calculate -metricA = Basically gets the average of COLUMN_A by a specific category , dax formula used: metricA = CALCULATE(AVERAGE(Table1[COLUMN_A ]) , Table1[COLUMN_B] = "A") This metric works fine too, for the moment. -MetricB: Now is where we get to the problem, this metric, should do two things: 1) for each month, calculate metricA (that is essentially, an average with a filter) it should get the average of only that month's data, and afterwards multiply it by the Number_of_days_of_month. It should do this in it's own context for each month. 2) Depending of the month we are working on the graph ( we are creating a matrix visual, that should represent the data like this:) It will add all the metricA of each month up to that point, and then divide them by the corresponding SUMDaynumsofmonth , each month in it's own context. Each number that we see in the row, is basically the SUM(metricA's of all months up to that point) / SUMDaynumsofmonth ( Essentially the sum of all the days that the months up to that point, have). So how did I go about this? MetricB = // Part 1)first we specify that we want metricA, and we calculate it on it's own context as a variable, for each month// VAR presupuesto_a_calcular = [metricA] VAR Pres_january = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 1) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes]= 1)) VAR Pres_february = ((CALCULATE(presupuesto_a_calcular , datetable[Mes] = 2) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 2))) VAR Pres_march = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 3) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 3)) VAR Pres_april = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 4) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 4)) VAR Pres_may = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 5) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 5)) VAR Pres_june = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 6) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 6)) VAR Pres_july = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 7) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 7)) VAR Pres_august = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 8 * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 8)) VAR Pres_september = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 9) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 9)) VAR Pres_october = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 10) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 10)) VAR Pres_november = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 11) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 11)) VAR Pres_december = (CALCULATE(presupuesto_a_calcular , datetable[Mes] = 12) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes] = 12)) RETURN //Part2) If statements that make it so, that depending of what month we are in, we will add our precalculated variables created earlier, and divide it by the corresponding SUMDaynumsofmonths of that month)// IF(MAX(datetable[Mes]) = 1 ,DIVIDE(Pres_january ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 1)), IF(MAX(datetable[Mes]) = 2 , DIVIDE((Pres_january + Pres_february) , CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 2)), IF(MAX(datetable[Mes]) = 3 , DIVIDE((Pres_january + Pres_february + Pres_march) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 3)) , IF(MAX(datetable[Mes]) = 4 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 4)), IF(MAX(datetable[Mes]) = 5 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april + Pres_may) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 5)), IF(MAX(datetable[Mes]) = 6 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 6)) , IF(MAX(datetable[Mes]) = 7 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 7)), IF(MAX(datetable[Mes]) = 8 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 8)), IF(MAX(datetable[Mes]) = 9 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august + Pres_september) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 9)), IF(MAX(datetable[Mes]) = 10 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august + Pres_september + Pres_october) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 10)), IF(MAX(datetable[Mes]) = 11 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august + Pres_september + Pres_october + Pres_november) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 11)), IF(MAX(datetable[Mes]) = 12 , DIVIDE((Pres_january + Pres_february + Pres_march + Pres_april +Pres_may + Pres_june + Pres_july + Pres_august + Pres_september + Pres_october + Pres_november + Pres_december) ,CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 12))) ) ) ) ) ) ) ) ) ) ) ) Here is where my code fails, for some reason it doesn't work, returning a smaller division that it should. I Have Applied this process , and it works outside of the big metricB formula, if I do this, it works: januaryexample = CALCULATE(Table1[MetricA], datetable[Mes] = 1) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes]= 1) februaryexample = CALCULATE(Table1[MetricA], datetable[Mes] = 2) * CALCULATE(MAX(datetable[NºDaysinMonth]) , datetable[Mes]= 2) MetricBforFebruary = DIVIDE(([januaryexample] + [februaryexample]) , CALCULATE(MAX(datetable[SUMDaynumsofmonths]) , datetable[Mes] = 2)) When I break it down in three metrics like this the desired result is returned for february in MetricforFebruary, but not so if I do it for all months in MetricB, What is the reason for this?Concatenation using variables
I need to have a result column in my report be a concatenation of text and conditionally, 1 or 2 addtional text 'values'. "Donor+xxxx" - in every row conditionally, if donor # from table 1 matches a donor # from table 2, I need to concatenate "+yyy" Further, if the State in another table = a certain value, additional concatenate "+zzz' Both the yyy & zzz are optional End results could be any of: Donor+xxxx Donor+xxxx+yyy Donor+xxxx+zzz Donor+xxxx+yyy+zzz yyy and zzz are not present in any table, just text values to concatenate. I've been playing with using variables and concatenations and Related, but no success.
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.
