Create Dynamic Measure Tables using Values from Slicer
I have a Dynamic Measure Table and need to pass in the slicer value. Unfortunately the value of the slicer is not being passed into it. I tried creating a measure to select value from the slicer and input into the dynamic table and still no luck. The output of the Dynamic table is displayed via a Table Visualization. I tested the Year and Month Measures in cards and they are fine. Has anyone come across this issue before? Selected Year = SELECTEDVALUE('Date'[Year]) Selected Month = SELECTEDVALUE('Date'[Monthnumber]) TestMetricsTable = VAR selectedyear = [Selected Year] VAR selectedmonth = [Selected Month] VAR DataTempTable = DATATABLE ( "Category", STRING, "Year", INTEGER, { { "none", 0 } } ) // RETURN SELECTCOLUMNS ( DataTempTable, "Category", "Selected Date", "Year", selectedyear, "Month", selectedmonth )DAX Dynamic table / context
Hi, I'm getting stuck on a DAX formula when creating dynamic table based on this requirement: -Getting all rows where the [AsOfDate] is lesser or equal to the date selected -Getting all rows where the [Launch Year]-1 is equal to the year of the date selected -Only getting latest / MAX [AsOfDate] For example: I first created the logic in SQL since I'm more conformable than DAX then try to translate it: CREATE TABLE #TemporaryTable ( LaunchYear INT ,Period INT ,AsOfDate Date ,BCount INT ,ID VARCHAR(4) ); INSERT INTO #TemporaryTable (LaunchYear,Period,AsOfDAte,BCount,ID) VALUES ('2022','1','2/1/2022','12','ASD') ,('2022','2','3/1/2022','5','ASD') ,('2022','3','4/1/2022','3','ASD') ,('2022','4','5/1/2022','2','ASD') ,('2022','5','6/1/2022','123','ASD') ,('2022','6','7/1/2022','14','ASD') ,('2022','7','8/1/2022','16','ASD') ,('2022','8','9/1/2022','1','ASD') ,('2022','1','6/1/2022','9','FGH') ,('2022','2','7/1/2022','3','FGH') ,('2022','3','9/1/2022','4','FGH') ,('2022','1','11/1/2022','12','JKL') ,('2022','2','12/1/2022','5','JKL') ,('2022','3','1/1/2023','3','JKL') ,('2022','4','2/1/2023','2','JKL') ,('2023','1','1/1/2023','0','XXX') DECLARE @DateVar DATE = '2023-01-01'; --'2023-02-01'; --'2023-01-01'; SELECT LaunchYear ,Period ,AsOfDAte ,BCount ,ID FROM ( SELECT * ,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID, AsOfDate DESC) RowNb FROM #TemporaryTable WHERE AsOfDate <= @DateVar AND LaunchYear = YEAR(@DateVar) -1 ) A WHERE RowNb = 1 DROP TABLE #TemporaryTable; Above SQL is behaving as expected and I arrived to this DAX formula: Dynamic Table = VAR FilterTable = FILTER( 'Sheet1', 'Sheet1'[AsOfDate] <= [DateSelected] && 'Sheet1'[LaunchYear] = YEAR([DateSelected])-1 ) VAR AddRowNumber = ADDCOLUMNS( FilterTable, "RowNb", ROWNUMBER ( FilterTable, ORDERBY ( 'Sheet1'[ID], ASC, Sheet1[AsOfDate], DESC), PARTITIONBY ( 'Sheet1'[ID]) ) ) VAR FilterFirstRow = FILTER(AddRowNumber,[RowNb]=1) VAR Result = SELECTCOLUMNS( FilterFirstRow, "RowNb",[RowNb], "AsOfDate",Sheet1[AsOfDate], "BCount",Sheet1[BCount], "ID",Sheet1[ID], "LaunchYear",Sheet1[LaunchYear], "Period",Sheet1[Period] ) RETURN Result It's working fine when I evaluate it and 'hardcode' the date that is filtered: But it's not working when I create it on the report view: - DateSelected measure seems correct - DateSelected = SELECTEDVALUE(Sheet1[AsOfDate]) - Tab result is all wrong The slicer is having a Year-Month column from my Date table that seems to have a proper relationship with the 'Sheet1' table I guess my issue is somewhere around my DateSelected measure / relationship with the Date table not able to have the correct context but I don't understand why obviously.
Add New Dynamically Updating Column To Data
Hi, I'm relatively new to using DAX in PowerBI and have turned to ChatGPT for some help but it's repeatedly giving me 2 solutions, neither of which work! The Data: I have just one data table of inbound calls into a call centre. Columns include: StartDateTime, Agent Name, Caller Number (among others). Inbound calls can be answered by different agents, so a caller number may appear 20 times on 20 different datetime occasssions and the caller may have spoken to 5 different agents. StartDateTime AgentName CallerNumber 02/09/2023 19:01 Bob Caller01 02/09/2023 19:15 Alice Caller01 07/09/2023 15:33 Alice Caller02 13/09/2023 12:04 Bob Caller01 I have created some calculated fields including: MinCallStartDateTime (returns the lower value of the date filter applied to the dashboard). MaxCallStartDateTime (returns the upper value of the date filter applied to the dashboard). MaxCallsMade (returns count of caller number based on date filters applied to dashboard). FirstCall (returns the datetime of the first call from the selected Caller Number based on the date filters applied to the dashboard). For Caller01 MinCallStartDateTime 01/09/2023 MaxCallStartDateTime 15/09/2023 MaxCallsMade 3 FirstCall 02/09/2023 19:01 What I want to achieve: 1) I want to know which agent answered the FirstCall "FirstCallAgent" - my dashboard currently returns the FirstCall PER agent (for each Caller Number Selected), I just care about who was the first agent to answer the very first inbound call (dependent on date filters applied to the dashboard). 02/09/2023 19:01 Bob 02/09/2023 19:15 Alice (this currently shows but I don't care about it) If possible it would be ideal to create a new column called "CallTally" to the table which can be updated to count each inbound call from that number dynamically to update based upon any date filters applied to the dashboard. StartDateTime AgentName CallerNumber CallTally - may change if date filter changes 02/09/2023 19:01 Bob Caller01 1 02/09/2023 19:15 Alice Caller01 2 07/09/2023 15:33 Alice Caller02 1 13/09/2023 12:04 Bob Caller01 3 2) "RepeatCallPerc" - The goal is to find out which agent is best at resolving customer problems meaning there will be fewer repeat calls / MaxCallsMade from Caller Number where one agent answered the very first call, relative to if another agent answered that first call (since a call can be answered by any agent). Bob 100% (100% of calls, where Bob answered the first call, called in at another time)(Caller01) Alice 0% (0% of calls, where Alice answered the first call, called in at another time)(Caller02) The best way I can think about doing this is to calculate: for each agent - SUM the total instances of CallTally= 1 (according to the new column created as part of step 1). Then divide this by the SUM of the total MaxCallsMade for caller numbers whereby the agent answered the FirstCall (regardless of which agents answered future calls) - does that make sense? Extra: 3) A third goal would be to isolate which agent answered the last call "LastCallAgent" from each caller number (meaning that agent was the one to resolve that query) - to be able to sum this up and see that calls handled by Agent X are more likely to be resolved (and therefore the customer is less likely to call in) compared to calls handled by Agent Y. Expressing this as a percentage for comparison reasons would also be useful "LastCallAgentPerc" = Count LastCallAgent / TotalLastCalls = SUM("LastCallAgent"). But again this would require updating the above table /column tallying inbound calls within the filtered date range. Bob 50% (50% calls Bob handled were "last calls" (Caller01) Alice 50% (50% of calls Alice handled were "last calls" (Caller02) Anyway, I have absolutely no idea how to begin this! Or if it's even possible. I would appreciate any insight and expertise you may be able to add to this. Or if you can add an easier way to think about this. Thanks.
Dynamic Field in Slicer based on Field Parameter selection
Hi PBI Community I have been stuck for a while on trying to build upon my Field Parameter into a slicer. I have a Field Parameter that switches between four columns (Min Quantity, Max Quantity, Avg Quantity, Confirmed Quantity). I need a Slicer / Filter that is populated by the field (4 columns, Min Price, Max Price, Avg Price, Confirmed Price), based on the selection in the field parameter. So e.g, if i were to select "Min Quantity" in my Field Parameter, the slicer would contain the "Min Price" column and so forth. I have looked into combining IF() and SWITCH(), but the Slicer won't accept the measures i have created as valid. All help is appreciated. Kind regards, Andreas
Dynamic Cumulative Totals on Pivoted Table
I have a table with pivoted data as shown below. I want to create a display a cumulative staff totals by year, for either of the Attributes selected on a Slicer object (education level or gender). How do I design a solution (a cumulative total) that re-calculates cumulative totals based on user selections in a Slicer? So far, I've used a standard cumulative total calculation, but it does not provide accurate results: CumulativeStaffTotal = CALCULATE( SUM(Merge1[netchange]), FILTER(ALLSELECTED(Merge1), Merge1[Value]=EARLIER(Merge1[Value]) && Merge1[year]=EARLIER(Merge1[year]) )) What approach would you use to solve this problem?
DAX macro / dynamically switch table to be used in measures
Hi, I am trying to dynamically switch the tables used for specific DAX measures. Ex: I want to know number of rows and sum of a specific column for every table I have imported (assuming each table has the same column to sum) Let's say I have 3 tables so far... Table1 Table2 Table3 Let's say I have two measure... NumberOfRows ColumnSum Let's say I have a created table that lists the tables... TableID Table Name 1 Table2 2 Table2 3 Table3 WHAT I CURRENTLY HAVE: The measures look like this... NumberOfRows = SWITCH(Table[ID], 1, COUNTROWS(Table1), 2, COUNTROWS(Table2), 3, COUNTROWS(Table3)) ColumnSum = SWITCH(Table[ID], 1, SUM(Table1[Column]), 2, SUM(Table2[Column]), 3, SUM(Table3[Column])) Currently, if I add new tables to my report, I add the new table expression to each measure! WHAT I WANT: To only have to update one DAX measure and all the other measure update as well. EX: having a dax measure called "TableUsed" and all my measures use that table TableUsed = SWITCH(1, Table1, 2, Table2, 3, Table3) NumberOfRows = COUNTROWS(TableUsed) ColumnSum = SUM(TableUsed) This way when I add new tables, all I have to update is the TableUsed measure, and not every single measure it uses. Right now I don't know how to havea table be the return value of a measure or how to create any sort of macros / functions for DAX formulas to dynamically change the "text" used in the DAX. If anyone knows any solution or has any input... Please provide! Thank you.Calculation with non-visulated data
Hello together Here is my initial situation: Value Date Payment PC IRR Purchased Effective Duration 24-Okt-2022 -7'457'843.06 2.019226 6.97 25-Okt-2022 -14'827'458.33 1.367932 13.28 25-Okt-2022 -7'237'375.00 2.027469 8.52 25-Okt-2022 -17'468'035.65 1.358039 12.31 26-Okt-2022 -100'500'000.00 1.464718 14.21 27-Okt-2022 -6'982'216.67 1.288672 11.41 23-Nov-2022 -15'121'041.67 1.227285 23-Nov-2022 -161'291'111.11 1.227285 06-Dez-2022 -25'269'000.00 1.037119 16.47 13-Dez-2022 -2'454'739.68 1.361915 8.56 13-Dez-2022 -35'924'916.66 1.049181 11.27 13-Dez-2022 -2'484'160.00 1.04288 11.41 15-Dez-2022 -35'094'884.77 1.12798 12.18 15-Dez-2022 -30'135'718.18 1.118771 8.72 19-Dez-2022 -30'141'666.67 1.109334 8.70 19-Dez-2022 -35'303'666.66 1.074108 12.16 28-Dez-2022 -49'350'000.00 1.186102 16.32 I have daily data here, sometimes 2 entries per day (cf. month of November). in the end, i want to have this format: MV Yield Duration DEZ -246'158'752.62 1.11 12.42 NOV -176'412'152.78 1.23 0 OKT -154'472'928.71 1.49 13.16 this is how the calculation works: Value Date Payment PC Payment per month Weighted Payment IRR Purchased Weighted Yield Value Date Effective Duration Weighted Duration 23-Nov-2022 -15'121'041.67 -176'412'152.78 0.09 1.227285 0.11 23-Nov-2022 0 23-Nov-2022 -161'291'111.11 -176'412'152.78 0.91 1.227285 1.12 23-Nov-2022 0 Payment per month = SUM(Payment PC) | from a single month Weighted Payment =divide(table[Payment PC] , table[Weighted Payment]) Weighted Yield = Table[Weighted Payment] * Table[IRR Purchased] Weighted Duration = Table[Weighted Payment] * Table[Effective Duration] for all calculations the daily values have to be used, but i only want to visualise the monthly results. the monthly yield is = sum(Weighted Yield). does anyone know if this is possible? Preferably with measures? As it should be dynamic when i apply e.g. filters and not all data is needed. Thanks a lot!
