keepfilters

9 Topics

Proper Calculation of Facts/Values from Dimensional Table
Source ( Download Here ) Userstory: Imagine you have big star shema model and important values/facts in dimensional Tables. And you don't want to mess up your fact table with multiple low cardinality columns. Question: How to proper handle these dimTable facts/values? Simplified Example: Question: How to get DESIRED YearVolume (because by default it gives 2200 (Totals) in each cell)?
Solved
Anonymous
2 years ago Place DAX Commands and Tips
1.7KViews
0likes
5Comments
Taking the Average of an Average Measure (Across a Category)
This is useful when you need to take the average of a measure (that is an average itself) across a category of data, for data points that occurred at different dates. (The date part is optional but can be useful when you are working with rolling data) For example, let’s say you have 3 machines, and each machine has a different number of products that it makes, and each product has its own average error rate. The catch is that the machine began manufacturing the different products on different days. And you want to know the average product error rate for each of the 3 machines, 2 quarters after it started manufacturing each of its individual products. Obviously it would be a lot easier if the machine started manufacturing all of its products at the same time, and each machine made the same amount of products, but life gets a little messy sometimes and we just have to roll with it. SO, here we go. Where to start? This is assuming your starting data has a column with machine type, product type, and manufacturing start date, and you also have the error rates organized by products and quarters. You may need to create relationships depending on how your data is set up. Create date measures for the 2 quarters after manufacturing date. Make a new column in your query first, using date.addquarters(column,1), and call it "1 Quarters After Mftg Start." Now, create a simple measure in your report: 1 Qtrs After Mftg Start = min('table'[1 Quarter After Mftg Start]) Do the same thing for 2 Quarters After. Since you probably want a rolling average instead of a snapshot, create a measure that averages the 2 quarters of error rates (this is assuming your error rates are captured in quarters. If not, you can use months, days, etc. for this problem). Avg Error Rate 2 Qtrs After = (CALCULATE(SUM('Table'[Error Rate]),FILTER(ALL('Table'[Date]),'Table'[Date]=[1 Quarter After Mftg Star]))+CALCULATE(SUM('Table'[Error Rate]),FILTER(ALL('Table'[Date]),'Table'[Date]=[2 Quarters After Mftg Start])))/2 This gives you the 2 quarter rolling average error rate for each product, 2 quarters after its manufacturing start date. Take the average of these product error rate averages, to give you an average error rate 2 quarters after product start manufacturing date for each machine. To do this, you can create a quick measure, average per category. Where the field is the measure you just created [Avg Error Rate 2 Qtrs After] , and the category is "Product." This might seem counterintuitive because you are wanting to group by machine, but if you categorize by machine, the measure is only going to divide by 1 since there is only 1 machine per category. Using "Product" as the category tells the measure to divide by the number of products in the category which will give you an accurate average per machine. Here is what the DAX looks like. Avg Error Rate 2 Quarters After average per Product = AVERAGEX( KEEPFILTERS(VALUES('Table'[Product])), CALCULATE([Avg Error Rate 2 Quarters After])]) ) Now, when you put all this onto a table, make sure to include a "subtotal" line if you want to see the average per machine. When you put this measure onto a bar graph with the machines as your axis, it should show you the average error rate per machine 2 quarters after manufacturing start dates. I hope this was helpful. I have been trying to solve this puzzle for a while now and thought I would share how I cracked it. Let me know if you have any questions!
Solved
Anonymous
3 years ago Place DAX Commands and Tips
1.2KViews
1like
1Comment
Different results of All and Summarize function on a column both combined with Keepfilters
Hello, I am new to DAX and have recently completed the evaluation concepts. I am wondering why following yield different result from the other function. Here is the link to access pbix on which i am working on. https://drive.google.com/file/d/17uSqXKk-zgmFlRF9XLDnYpsIJZQxhemt/view?usp=sharing DAX 1 DAX 2 I just replaced All filter with Summarize function. I understand that when All function is used with a column reference then it yields unique values of the column. While Summarize function (when applied on a column) provides unique values. Both these functions being filter parameters of Calculate functions work in original filter context environment. The way i understand keepfilters works, it intersect the values of columns in original filter context and the values derived of the same column in keepfilters function. Ideally, i was expecting them to yield the same result. However, in this case as we see, it is yielding different output. Thank you for your time in going through the question and helping me out !
Solved
ARU_
3 years ago Place DAX Commands and Tips
3.3KViews
0likes
6Comments
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 😄
Solved
Anonymous
3 years ago Place DAX Commands and Tips
1.3KViews
0likes
2Comments
Measure that keeps one filter while removing another filter both on the same table
Hi I need some help with a DAX measure that removes one filter while keeping another filter both on the same table. The datamodel is about 6 companies each having multiple locations in different municpalities. And each location has a particular capacity. The size of an organisation is the sum of the capacity of all its locations. In my report I have a dynamic ranking, showing the Top 5 or Top 3 companies in size, depending on whether the user has selected a particular municpality or not (Top 5 when no municipalities are selected and the entire country is shown and Top 3 when one or more municpalities are selected). Therefore the Organisations table is filtered in two ways: 1. Ranking within Top 5 or within Top 3 2. The selected municpalities in the slicer I would like to calculate the marketshare in the selected area (either one or more municipalities or the entire country when no municipalities are selected). Therefore I need to remove the filter that determines to show or hide a particular organisation in the Top N Bar chart. And at the same time I need to keep the filter resulting from the selected municpalities, in order to divide the capacity of the organisation in the selected area by the entire capacity in the selected area. I tried with ALL, REMOVEFILTER and KEEPFILTER, but I cannot figure out how to keep one filter and remove the other at the same time on the same table. And the REMOVEFILTER should point to a measure that determines whether to show an organisation with a particular ranking or not. But REMOVEFILTER accepts only columns. Any help is appreciated. Current DAX measure (that doesn't work properly): Market_share = VAR Grand_total_capacity_selectedarea = SUMX ( FILTER (ALL('Locations'), 'Locations'[Location_type] = "A") , 'Locations'[Capacity] ) VAR Company_capacity = [Sum_of_Capacity] RETURN DIVIDE (Company_capacity, Grand_total_capacity_selectedarea, "") Other measures: Show company = VAR N = IF ( ISFILTERED ( Municipalities[Name] ) = TRUE, 3, 5 ) VAR Show_indicator = IF ( RANKX ( ALL ( Companies ), [Sum_of_Capacity] ) <= N, 1, 0 ) RETURN Show_indicator
Sebastiaan99
4 years ago Place DAX Commands and Tips
486Views
0likes
1Comment
Respecting filter context Issues, please Help!
Hello Everyone, I think I'm having filter context issues! any help or hints would be very much appreciated! I have this table called Ranking_New_Employee Team or IA NET ranking for seniority Total Net to total Adam 329 New Employee 2502 13% Jessica 236 New Employee 879 27% Ben 181 New Employee 1105 16% Mark 164 New Employee 905 18% Dumanowski 145 New Employee 2843 5% Ewing 116 New Employee 854 14% Vooys 104 New Employee 2970 4% Donahue 82 New Employee 912 9% Mazigi 70 New Employee 451 16% Gonzalez 66 New Employee 279 24% Sherwick 63 New Employee 1649 4% Blaine 48 New Employee 1022 5% Andrew 45 New Employee 227 20% Cordel 40 New Employee 1711 2% Garry 38 New Employee 852 4% Pelleter 31 New Employee 1305 2% Catherine 30 New Employee 1008 3% Dan 27 New Employee 373 7% Reid 24 New Employee 182 13% Scott 19 New Employee 116 16% Christian 15 New Employee 1020 1% Colin 10 New Employee 236 4% Steven 3 New Employee 993 0% I have created this measure to be able to get top 10/bottom10 by Net value. TOP_New_emp = VAR raking_asc = RANKX ( ALL ( 'Ranking_New employee'[NET], 'Ranking_New employee'[Team or IA]), CALCULATE ( SUM ( 'Ranking_New employee'[NET] ) ) ) VAR ranking_desc = RANKX ( ALL ( 'Ranking_New employee'[NET], 'Ranking_New employee'[Team or IA]), CALCULATE ( SUM ( 'Ranking_New employee'[NET] ) ), ,ASC ) RETURN SWITCH ( SELECTEDVALUE ( View_New_Employee[Type] ), "Bottom 10", IF ( ranking_desc <= 10, SUM ('Ranking_New employee'[NET] ),blank()), "Top 10", IF ( raking_asc <= 10, SUM ( 'Ranking_New employee'[NET]) , blank() )) This is the result I get when I select top 10. but, of course, when I select bottom10, only the bottom 10 get selected. Team or IA Top_New_emp Adam 329 Jessica 236 Ben 181 Mark 164 Dumanowski 145 Ewing 116 Vooys 104 Donahue 82 Mazigi 70 Gonzalez 66 *****The Issue starts here***** Now, I have created this measure to get a % Net to total but when I drag this measure to the result above, I get this : Team or IA Top_New_emp Net to total Adam 329 13% Jessica 236 27% Ben 181 16% Mark 164 18% Dumanowski 145 5% Ewing 116 14% Vooys 104 4% Donahue 82 9% Mazigi 70 16% Gonzalez 66 24% Sherwick 4% Blaine 5% Andrew 20% Cordel 2% Garry 4% Pelleter 2% Catherine 3% Dan 7% Reid 13% Scott 16% Christian 1% Colin 4% Steven 0% But, this is the desired result : Team or IA Top_New_emp Net to total Adam 329 13% Jessica 236 27% Ben 181 16% Mark 164 18% Dumanowski 145 5% Ewing 116 14% Vooys 104 4% Donahue 82 9% Mazigi 70 16% Gonzalez 66 24% I would like the new calculated measure to respect Top_New_emp filter. In other words, I want to get only Net to Total values that correspond to Top_New_emp whether I select Top 10 or Bottom 10. For example, when I select Top 10, I don't want the underlined values (refer to the table above) to appear. Thanks in advance
PBIFX200
5 years ago Place DAX Commands and Tips
668Views
0likes
1Comment
Sum in Matrix visual with filters from multiple tables
I will try to put my question out again as there were some issues with posting when I updated it. I am trying to sum data in a Matrix with filters from multiple tables and am unable to determine the correct measures to use. What I am looking is result similar to the below: I am unable to share the pbix, below is a view of the four separate tables of data I import into Power BI: The data structure and measures I have tried so far: 1) Count ECountry = Calculate(DistinctCOUNT(Sheet1[ECountry]),KEEPFILTERS(Sheet1)) 2) Epic Size = Calculate(Sum('Sheet1 (4)'[ESize]), KEEPFILTERS(Sheet1),('Sheet1 (4)')) 3) Epic Size Alloc = Divide([Epic Size],(SUMX(Sheet1,Sheet1[Count ECountry]))) Any help with calculations or guidance on data structure is much appreciated! Melody
molinamelody
5 years ago Place DAX Commands and Tips
628Views
0likes
0Comments
Need help with DAX code, performance problem
See the description of the problem below, "Metric_to_Show" in the red box. As you can see I have one solution that is working fast if I use the actual metric, but when I replace the actual metric with my dynamic selection it is slow. So my Q is why is it not working with ’Dynamic_Metrics’[Metric to show]?
Solved
Anonymous
5 years ago Place DAX Commands and Tips
1.2KViews
0likes
5Comments
DAX - KEEPFILTERS not showing correct results - arbitrarily shaped set
I am doing some training work in AdventureWorksDW database. I have two measures: Sum of SalesAmount = SUM(FactInternetSales[SalesAmount]) MonthlyAverageSales = AVERAGEX ( VALUES(DimDate[EnglishMonthName]), [Sum of SalesAmount] ) I have developed the above report, choosing only two years 2006 and 2007, and only 4 months using slicers. While the measure [Sum of SalesAmount] shows the correct value, the other measure [MonthlyAverageSales] shows incorrect data in the Total row; however, both measures are right, when aggregated for each year 2006 and 2007. The measure [MonthlyAverageSales] at the Total row should actually be $ 5,916,696.73/8, which is $ 739,587.09. (8 in denominator represents 8 months - Jan,Feb,Nov,Dec for each year - 2006 and 2007) Instead, what I see is $ 5,916,696.73/4, which is $1,479,174.18; (4 in denominator represents 4 months- Jan,Feb,Nov,Dec, combining both years together) Now, I have changed the formula, and have created a new measure: MonthlyAverageCorrect = AVERAGEX ( KEEPFILTERS(VALUES(DimDate[EnglishMonthName])), [Sum of SalesAmount] ) I still do not see any change. Instead of getting $ 739,587.09, I still see $1,479,174.18. Where am I in error ?
Solved
snph1777
6 years ago Place DAX Commands and Tips
7.2KViews
0likes
13Comments