Forum Discussion
Filtering table
Hi,
Would be interested in learning about common use cases for using filtered table (FILTER, ALL, VALUES, DISTINCT, RELATEDTABLE). It will greatly help me understand the syntax if I have the full context.
Can anyone share?
Thanks!
Just came up with one in the last day or so helping another user out. The challenge was to count the distinct types a value appears in a column, where the values could be delimited within the field. I.e. the answers could have "A/B/C" and "B/C/D" and i'd need to count A, B C,and D separately and distinctly across all rows.
Solution required the creation of a dummy table to cross join against. In this case a filter was required. Naturally a calculation was not suitable. Here is the code:EmployeeCount = COUNTX( //This is the row that does the count SUMMARIZE( //This will make the distinct values in our column ADDCOLUMNS( //This creates the calculated column of our Employee Names FILTER( //This cuts down the dummy table to only be the size of the number of Names we have CROSSJOIN( //This Merges our Dummy Table with the Employee Names SUMMARIZE( //This creates each 'Employee Name' row Table1, Table1[Employees], Table1[Name], "NamesCnt", 1 + len(Table1[Employees]) - len(SUBSTITUTE(Table1[Employees], "/", "")) //Count of Slashes ), DummyTbl ), DummyTbl[Dummy] <= [NamesCnt] ), "SubName", PATHITEM( // This function splits up the Employee names to be placed in each row SUBSTITUTE(Table1[Employees], "/", "|"), DummyTbl[Dummy] ) ), [SubName] ), [SubName] )
7 Replies
I found very interesting videos about DAX language. this might useful for you.
Thank you dilumd! Indeed very useful videos.
Here are some comments on some of the mentioned formulas:
FILTER :- I don't tend to use this one much, i often go for CALCULATE instead. The idea is to take the data in the current context and filter it further to get a specific set of data. For example, my current context might be the current year and i want so sum up all of the Monday's. So my calculate statement would be a Sum, where the day of the week is equal to Monday.
ALL :- This one is useful whenever you know a measure will be within a context, which will be a problem because you need to consider all of your data. Imagine you have a matrix with a list of dates and you want to sum up all of the days that share the same Year. So you would use a Calculate statement, use the sum formula, then in your filter section you firstly make use of ALL to state you want all of the data, then we make the 2nd filter statement that the Years must match
Distinct/Values :- This one is a tricky one to visualise. The first place i ever used it was to build a list of items to perform calculations on. Say you wanted to get an Average figure for each month, however your figures are stored daily. So you would need a way to calculate the Sum monthly, then perform an average on the result of the sum. In this case i use a date table which has the Month/Year as a single field. I then run a AVERAGEX with the first parameter as VALUES('Dim Date Table'[YearMonth], then the second paramenter is the SUM formula of my values.
RelatedTable :- Not one i've really used. i'll allow others to comment on this.Many thanks, Anonymous!
To make sure I understand... How would Table filter be different from a calculated measure?
Here is some sample data to demonstrate my question:
1. I manually entered Volume data of items per 3 dates a year as follows:
2. I then created a Matrix visual and configured the date to only show the year:
3. To show only 2017 data, I used the following measure: Sum Items 2017 = CALCULATE(sum('Sample'[# of Items]),YEAR('Sample'[Date])=2017)
Sum Items 2017 = CALCULATE(sum('Sample'[# of Items]),YEAR('Sample'[Date])=2017)
My question is then, what would a filtered table allow me to do, that a calculated measure would not?
They do similar things but for different reasons. Calculate is like saying "Run this calculation, but using this context". Filter is saying, please return a table with this additional filter context. That sounds really similar, but thats often the case with tools. 2 tools can have a good overlap but when you find that use case where 1 just doesn't work, suddenly the other tools existance makes more sense.
