cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
asaft Regular Visitor
Regular Visitor

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Ross73312 Super Contributor
Super Contributor

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

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

7 REPLIES 7
dilumd Established Member
Established Member

Re: Filtering table

I found very interesting videos about DAX language. this might useful for you.

 

Ross73312 Super Contributor
Super Contributor

Re: Filtering table

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


asaft Regular Visitor
Regular Visitor

Re: Filtering table

Thank you @dilumd! Indeed very useful videos.

asaft Regular Visitor
Regular Visitor

Re: Filtering table

Many thanks, @Ross73312

 

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: 

Full Details.png

 

2. I then created a Matrix visual and configured the date to only show the year: 

Calculated Details.png

 

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)

 

Filtered Year.png

 

My question is then, what would a filtered table allow me to do, that a calculated measure would not?

 

 

Ross73312 Super Contributor
Super Contributor

Re: Filtering table

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


asaft Regular Visitor
Regular Visitor

Re: Filtering table

Thanks, @Ross73312.  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

Highlighted
Ross73312 Super Contributor
Super Contributor

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

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 289 members 2,856 guests
Please welcome our newest community members: