Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 ful...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    9 years ago

    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]		
)
Anonymous's avatar
Anonymous
Not applicable
9 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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]		
)