cancel
Showing results for
Did you mean:
asaft 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

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

Proud to be a Datanaut!

7 REPLIES 7
dilumd Established Member

## Re: Filtering table

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

Ross73312 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.

Proud to be a Datanaut!

asaft Regular Visitor

## Re: Filtering table

Thank you @dilumd! Indeed very useful videos.

asaft 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: 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?

Ross73312 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.

Proud to be a Datanaut!

asaft 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

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

Proud to be a Datanaut!

Announcements #### 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.  #### 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

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 289 members 2,856 guests
Recent signins:
• RagulSandren • bhavesh7098 • HimparIn123 • vstopp • StephonLofton • sylpel • Ginger • chenthorn • tjn-work • haka • ncm_swhitehouse • AlliedBrooks • MChinwala • michaelou 