Forum Discussion
Filtering table
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] )
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.
Thanks, Anonymous. If anyone in this forum has a business case that leverages filtered table, and that could not have been achieved with calculated measures, please share. It would really help learning how to use such tables
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] )