Getting differences between 2 outputs in Dax
Some context: I am connected Direct Query to a dataset so i cannot use calculated columns or change the dataset in any way. I need to accomplish this in dax. I have a measure that shows me all the tickets touched by "Team1" Note: The "MyTeam" table contains many different ticket numbers but the same ticket number can also repeat in the table for each team that touches it is a new row in the table. TouchedByTeam1 = CALCULATETABLE(VALUES(MyTable[Ticket]), FILTER(MyTable, MyTable[Definition] = "Team Group"), FILTER(MyTable, MyTable[Team] = "Team 1")) I have another measure that gets me al the tickets Team 1 touches but doesnt not move to another team. FirstTeam1Touch = VAR LastTouchTeam = CALCULATE( MAXX(MyTable, MyTable[Team]), FILTER( MyTable, MyTable[Start] = CALCULATE(MAX(MyTable[Start]), ALLEXCEPT(MyTable, MyTable[Number])) ) ) VAR FirstTeam1Touch = CALCULATE( MIN(MyTable[Start]), MyTable[Team] = "Team 1", ALLEXCEPT(MyTable, MyTable[Ticket]) ) RETURN IF( LastTouchTeam = "Team1", FirstTeam1Touch, BLANK() ) I have been at this for the last couple weeks and havent figured it out. I put both of these measure in the same Measure as variables and have been trying to use "Except" to get the difference but nothing seems to work. This was my last attempt with everything in variables. Obvisously the Except doesnt work becuase the FirstTeam1Touch does not output a table being it is a calculate statement. VAR LastTouchTeam = CALCULATE( MAXX(MyTable, MyTable[Team]), FILTER( MyTable, MyTable[Start] = CALCULATE(MAX(MyTable[Start]), ALLEXCEPT(MyTable, MyTable[Ticket])) ) ) VAR FirstTeam1Touch = CALCULATE( MIN(MyTable[Start]), MyTable[Team] = "Team 1", ALLEXCEPT(MyTable, MyTable[Ticket]) ) var TouchedByTeam1 = CALCULATETABLE(VALUES(MyTable[Ticket]), FILTER(MyTable, MyTable[Definition] = "Team Group"), FILTER(MyTable, MyTable[Team] = "Team 1"), FILTER(MyTable, MyTable[Created] >= EOMONTH(TODAY(),-2)+1 && MyTable[Created] < EOMONTH(TODAY(),-1)+1)) var total = CALCULATETABLE(EXCEPT(TouchedByTeam1, FirstTeam1Touch)) Any help is greatly appreciated.
Help with filtering automatically the most recent date
Hi Power BI community, DAX/power BI newbie here. I need help with something I am trying to build in DAX for my organisation. Essentially, I want a dashboard that only shows the most recent date as a filter. The data point I want to show is "lost revenue" which is a calculated measure that uses other created measures to return a value. I have looked around the forums and can see that I can create a column or measure using the MAX function which I have done Most Recent Date Column = CALCULATE( MAX(clogs[Date]), ALL(clogs[Date]) ) This seems to generate the most recent date as a value for each row - which is what I wanted.. However, I can't seem to use this date to filter against my created measure "lost revenue". Maybe it is the way my data is structured? I thought to use CALCULATE and SUM function for the lost revenue with the recent day measure to filter by only the most recent date however that does not seem to work as I can't sum the lost revenue calculated measure. I want a dashboard guage visually to automatically show the most recent data without having to use the date slicers, so this will update every database refresh - which is daily in my organisation. As you might be able to already tell, I am still quite inexperienced with this DAX and get confused a lot with the various expressions. Any help would be most appreciated - and apologies in advance if my above question is not clear. Trent
Regarding Max Condition with multiple condition
Hi All, I have a table consisting of name, CRM number and a phase where I need to find the max value of the CRM number. I have three conditions here : I want DAX in PowerBi. If a name has only agreements in the phase then the max value should be of the highest CRM number in agreement. If a name consists of agreements, quotes and leads then also the max value should be of the highest CRM number in agreement. If a name doesnt have any agreements but has leads and quotes then the CRM should of the highest CRM number. I have tried many methods but couldnt find any solution please help me in this regard and its urgent !! I have attached a snap of the example, please have a look at it how the result should look !!
Create a custom static table using DAX
I am trying to design this chart in powerbi. The x axis contains different time limits. Currently this is design in excel and the table below is the source of data Average Tests March 2 April 1 - 28 4 April 29 - July 17 56 July 20 - Oct 9, 2020 63 Oct 12 2020-Nov 11, 2022 15 This table is created after getting average test from the same column on a different table below Date Number of Tests 03/09/20 1 03/10/20 2 03/11/20 1 03/12/20 0 Currently powerbi has access to the primary dataset below Date | Name| Race/Ethnicity | Result My inital thought was using DAX ROW or DATATABLE to create a Virtual or static table and use the average function to get the numbers. I am stuck!!! I will honestly appreciate any help provided.
