CALCULATETABLE to filter fact table using slicer to make multiple selections
I have a fact table containing a list of unique Curricula (Curricula ID) and associated Location Type and Locations. I need to present this in a visual table and filter this visual with a slicer containing only the six sites. I found a way to do this by splitting the fact table of All Curricula into Site Curricula, Regional Curricula and Global Curricula. The method uses one-many and many-many relationships in a data model. I suspect there may be a way to filter the All Curricula table using the same Site Slicer and a CALCULATETABLE. But I don't know how to begin. Thank you
CALCULATETABLE with SUMMARIZE and Dynamic Filter - Is this possible ?
Hi All, I am learning PowerBI and I am trying to create a table taking 3 columns from another table filtering the project number ( Information of another table) based on a selected value. CurrentProj = SELECTEDVALUE(Proj_CostSummaries[Project_Number]) The Code to create the table is the following : TimeSpent = CALCULATETABLE(SUMMARIZE(Proj_CostSummaries,Proj_CostSummaries[Period_Date],Proj_CostSummaries[TS_Total_Cost_Project_Currency],Proj_CostSummaries[Project_Number]), FILTER(Proj_CostSummaries,Proj_CostSummaries[Project_Number] = [CurrentProj])) I can see all the columns but for some reason the FILTER is not doing anything. If you can help me understand the issue that will be great!Applying multiple filters on table (with the goal of avoiding repeated filters)
I have a report with slow visuals (to the point that it runs out of memory) and I am trying to optimize some of the DAX measures. It is a big report, but the model for the part that I am having problems has 4 tables shown below: and the current measure is: Capacity = (CALCULATE(SUM(FactCapacityForecast[Value]), FILTER('Shared DW_DimVersion', EDATE(MIN(DimDate[Date]),-1)='Shared DW_DimVersion'[FCStartDate]), FILTER('Shared DW_DimAccount','Shared DW_DimAccount'[AccountNumber]="CALC0008"), DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5))) + CALCULATE(SUM(FactCapacityForecast[Value]), FILTER('Shared DW_DimVersion', EDATE(MIN(DimDate[Date]),-1)='Shared DW_DimVersion'[FCStartDate]), FILTER('Shared DW_DimAccount','Shared DW_DimAccount'[AccountNumber]="CALC0006"), DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5)))) * (DIVIDE( CALCULATE(SUM(FactCapacityForecast[Value]), FILTER('Shared DW_DimVersion', EDATE(MIN(DimDate[Date]),-1)='Shared DW_DimVersion'[FCStartDate]), FILTER('Shared DW_DimAccount','Shared DW_DimAccount'[AccountNumber]="CALC0017"), DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5))), CALCULATE(SUM(FactCapacityForecast[Value]), FILTER('Shared DW_DimVersion', EDATE(MIN(DimDate[Date]),-1)='Shared DW_DimVersion'[FCStartDate]), FILTER('Shared DW_DimAccount','Shared DW_DimAccount'[AccountNumber]="CALC0016"), DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5))))) * (DIVIDE( CALCULATE(SUM(FactCapacityForecast[Value]), FILTER('Shared DW_DimVersion', EDATE(MIN(DimDate[Date]),-1)='Shared DW_DimVersion'[FCStartDate]), FILTER('Shared DW_DimAccount','Shared DW_DimAccount'[AccountNumber]="CALC0005"), DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5))), CALCULATE(SUM(FactCapacityForecast[Value]), FILTER('Shared DW_DimVersion', EDATE(MIN(DimDate[Date]),-1)='Shared DW_DimVersion'[FCStartDate]), FILTER('Shared DW_DimAccount','Shared DW_DimAccount'[AccountNumber]="CALC0006"), DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5))))*-1)*-1 I believe this can be written in a more efficient and readable way. Right now the filters are applied on 3 tables ('Shared DW_DimVersion', 'Shared DW_DimAccount' and 'DimDate') in each of CALCULATE functions and 2 of those are just being repeated. I want to create a table variable where I first apply filters on 'Shared DW_DimVersion' and 'DimDate' only once and then use that variable to apply filter on 'Shared DW_DimAccount' afterwards. Maybe the final measure can be something like this: Capacity Optimized = VAR intermediate_table = CALCULATETABLE(FactCapacityForecast, //KEEPFILTERS( FILTER( //ALL ('Shared DW_DimVersion'[FCStartDate]), 'Shared DW_DimVersion', EDATE(MIN('DimDate'[Date]),-1)='Shared DW_DimVersion'[FCStartDate] ) // ) , KEEPFILTERS( DATESBETWEEN(DimDate[Date],MIN(DimDate[Date]),EDATE(min(DimDate[Date]),5)) ) //DATESINPERIOD('DimDate'[Date],MIN('DimDate'[Date]), 5, MONTH) ) RETURN (CALCULATE(SUMX(intermediate_table, FactCapacityForecast[Value]), KEEPFILTERS( FILTER( //ALL ('Shared DW_DimAccount'[AccountNumber]), 'Shared DW_DimAccount', 'Shared DW_DimAccount'[AccountNumber] = "CALC0008" ) ) ) + CALCULATE(SUMX(intermediate_table, FactCapacityForecast[Value]), KEEPFILTERS( FILTER( //ALL ('Shared DW_DimAccount'[AccountNumber]), 'Shared DW_DimAccount', 'Shared DW_DimAccount'[AccountNumber] = "CALC0006" ) ) )) * DIVIDE( CALCULATE(SUMX(intermediate_table, FactCapacityForecast[Value]), KEEPFILTERS( FILTER( //ALL ('Shared DW_DimAccount'[AccountNumber]), 'Shared DW_DimAccount', 'Shared DW_DimAccount'[AccountNumber] = "CALC0017" ) ) ), CALCULATE(SUMX(intermediate_table, FactCapacityForecast[Value]), KEEPFILTERS( FILTER( //ALL ('Shared DW_DimAccount'[AccountNumber]), 'Shared DW_DimAccount', 'Shared DW_DimAccount'[AccountNumber] = "CALC0016" ) ) ) ) * (DIVIDE( CALCULATE(SUMX(intermediate_table, FactCapacityForecast[Value]), KEEPFILTERS( FILTER( ALL ('Shared DW_DimAccount'[AccountNumber]), 'Shared DW_DimAccount'[AccountNumber] = "CALC0005" ) ) ), CALCULATE(SUMX(intermediate_table, FactCapacityForecast[Value]), KEEPFILTERS( FILTER( ALL ('Shared DW_DimAccount'[AccountNumber]), 'Shared DW_DimAccount'[AccountNumber] = "CALC0006" ) ) ) )*-1)*-1 The results are supposed to be used in a matrix alongside 'DimDate[Month]' values. I have been playing around with different functions, but cannot get the same number as the original measure. How can I get the correct measure? Thanks! Moe OwenAuger I think you have a good solution for this problem as well 😄
Append table with column from calculateTable.
I have the following table. I want use it to calculate the difference between the revenue of a given date, and that same date but one year ago, as to eventually create a visual in which I compare the revenue of Aug 2022 to Aug 2021, for instance. I have the following DAX expression for this: Revenue Growth = VAR prevDate = DATEADD('Growth Metrics'[Date], -1, YEAR) VAR diff = SELECTCOLUMNS( CALCULATETABLE('Growth Metrics', 'Growth Metrics'[Date] = prevDate), "Date", 'Growth Metrics'[Date], "Revenue Growth", 'Growth Metrics'[Revenue] - [Revenue]) RETURN diff['Revenue Growth'] The problem is that I cannot access 'Revenue Growth' from diff like this. How can I append the existing table with this column? Date is added in there because I tried using LOOKUPVALUE(), but to no avail.
comparing columns
hello, I'm trying to create a calculated table with data from one that doesn't have the other. I am doing Calculatetable ( FILTER (F_TABLE_1 , NOT CONTAINS (F_TABLE_2 , F_TABLE_2 [costumer_2 ] , F_TABLE_1 [costumer_1] , F_TABLE_2 [cod_2 ] , F_TABLE_1 [cod_1] ) this works, but first I want it to validate if the id of one is greater than the other I tried to do that switch (TRUE () , FILTER ( F_TABLE_2 , F_TABLE_2 [ID_costumer_2 ]) > FILTER ( F_TABLE_1 , F_TABLE_1 [ID_costumer_1]) , Calculatetable ( FILTER (F_TABLE_1 , NOT CONTAINS (F_TABLE_2 , F_TABLE_2 [costumer_2 ] , F_TABLE_1 [costumer_1] , F_TABLE_2 [cod_2 ] , F_TABLE_1 [cod_1] ) but that doesn't work, I want it to first validate if the customer id of f_table_2 is greater than the id_customer of f_table_1 and then do the other validations att: the customer id is the joining of the date in numeric format and a few more digits. With each passing day that number increases.Calculate all new customers with a second visit
Hi, I need to calculate the number of customers new in the previous month which have had 2 visits. Fact table: I also have a date dimension joined to the fact table which has an active relationship on visit date so TREATAS or USERELATIONSHIP may need to be used. New customer is defined by their reg date. So the dax should count customers that registered and had two visits in the previous full month. From the above, only customer 1 should be counted, as customer 2 didn't have a registration date in the previous month. Can anyone help?Get Current or Last Non-Zero Value
I am looking to create a DAX formula that gets the sum of an amount grouped by a category and date. If the sum amount is zero for that category and date then I want to get the last non-zero value. The current DAX formula that I have works great when only one single category exists, but when I add multiple categories it gets the last non-zero amount across all categories. Here is the data structure (Left = "DateDim", Right = "AmountFact"): Very simple model: Here are the measures: TotalAmount = SUM(AmountFact[Amount]) CurrentOrLastValue = IF( [TotalAmount] = 0, CALCULATE ( [TotalAmount], TOPN( 1, CALCULATETABLE( 'AmountFact', FILTER( ALL('AmountFact'), [TotalAmount] > 0 ), FILTER( ALL( 'DateDim' ), 'DateDim'[Date] < max( 'DateDim'[Date] ) ) ), 'AmountFact'[Date], DESC ) ), [TotalAmount] ) Here is an example of when only one category exists (Working as expected): Here is when multiple categories exist: As you can see above the most recent non-zero value is now the most recent non-zero value of ANY category. I believe that whats going on is that in the "CurrentOrLastValue" measure when I select the TOPN 1 to get the most recent non-zero record it is looking at ALL the records. I did a bunch research looking at how I could filter that CALCULATEDTABLE to only get the category of the current record without any luck. Hoping that someone can help me out here!
