Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zamurr
Employee
Employee

DAX - SelectColumns->Filter->Contains Syntax

Problem

 

I want to filter my dataset while performing a DAX query. The problem i am having is setting the formula up correctly.  From my understanding Contains takes a (table, comparision that returns T or F). Filter (table, condition). Maybe that is wrong.

 

Code

 

FlatTable = SELECTCOLUMNS(
	FILTER('build',
		CONTAINS('build',build[RelativeStartPath],"shell")
build,
"Index",build[Index],
"MachineID",build[Machine],
"UserAlias",build[Alias],
"Branch",build[Branch],
"Directory",build[StartPath],
"BuildCommand",build[CommandLineString],
"Razzle Cmd",build[Arguments],
"Clean",build[Clean],
"Parent",build[Parent],
"BuildTime",build[BuildTime],
"MachineType",LOOKUPVALUE(MachineInfo[Model],MachineInfo[MachineName],build[Machine]),
"Manager",LOOKUPVALUE(Shell[Org_Mgr],Shell[Org_User],build[Alias]),
"DM",LOOKUPVALUE(ShellDev[Org_DM],ShellDev[Org_User],build[Alias]),
"DateTime",build[Time]))

Thanks in advance

8 REPLIES 8
Greg_Deckler
Super User
Super User

Seems like you have a paren in the wrong place:

 

FlatTable = SELECTCOLUMNS(
	FILTER('build',
		CONTAINS('build',build[RelativeStartPath],"shell"))
build,
"Index",build[Index],
"MachineID",build[Machine],
"UserAlias",build[Alias],
"Branch",build[Branch],
"Directory",build[StartPath],
"BuildCommand",build[CommandLineString],
"Razzle Cmd",build[Arguments],
"Clean",build[Clean],
"Parent",build[Parent],
"BuildTime",build[BuildTime],
"MachineType",LOOKUPVALUE(MachineInfo[Model],MachineInfo[MachineName],build[Machine]),
"Manager",LOOKUPVALUE(Shell[Org_Mgr],Shell[Org_User],build[Alias]),
"DM",LOOKUPVALUE(ShellDev[Org_DM],ShellDev[Org_User],build[Alias]),
"DateTime",build[Time])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

It is good to know that PowerBI is trying to help me by automatically slamming a paran in there.

 

However, i am still get a syntax error. see the screenshot below.

 

 

dax.png

Hi zamurr,

The syntax error here should be the Selectcolumns function Syntax error. When using Selectcolumns, the first parameter is a table expression, and after that there are pairs of parameters consisting of:

  • A new column name
  • An expression returning a column from the table given in the first parameter

Using Filter as the first parameter in your expression, Selectcolumns already have a table syntax, then the second ‘builds’ should be a new column name, but based on what has been posted, there are two table syntax here.

We may check the selectcolumns function with the following reference.

Using SelectColumns() To Alias Columns In DAX

Besides, could you please share about what you would like to achieve? Along with some data sample, we could offer some additional suggestions regarding DAX expression.

Please post back if any further assistance needed.

Regards

After my last post i realized that i needed to treat my DAX created table as the data table for the FILTER functions. Find the bold and underlined text to see my changes.

 

 

FlatTable = FILTER(SELECTCOLUMNS(
build,
"Index",build[Index],
"MachineID",build[Machine],
"UserAlias",build[Alias],
"Branch",build[Branch],
"Directory",build[StartPath],
"BuildCommand",build[CommandLineString],
"Razzle Cmd",build[Arguments],
"Clean",build[Clean],
"Parent",build[Parent],
"BuildTime",build[BuildTime],
"MachineType",LOOKUPVALUE(MachineInfo[Model],MachineInfo[MachineName],build[Machine]),
"Manager",LOOKUPVALUE(Shell[Org_Mgr],Shell[Org_User],build[Alias]),
"DM",LOOKUPVALUE(ShellDev[Org_DM],ShellDev[Org_User],build[Alias]),
"DateTime",build[Time]), [Directory] = "shell")

 

 

Hi zamurr,

This one should work.

So what is your current situation?

Regards

It did not like the syntax. i actually moved on from this problem because i decided that i could filter with the slicers. I was hoping that creating a table using DAX would have made my querying faster, but that doesn't seem to be the case.

I am new to DAX, so I am working my way through a complex problem. I am looking to create a table from columns in other tables. Here is the description of what I am trying to do

 

http://community.powerbi.com/t5/Desktop/Combine-multiple-tables-into-one-table/m-p/60752#M24933

 

I understand the SelectColumns, but what i dont understand is how to implement filtering. I want to filter out data before i create my table.

 

Do i want to filter on the outside of the DAX query?

Had the same problem but the solution seemed straight forward after reading up the documentation.
Table 1 = Column1 with values A,B,C

The following gives all of Table 1 as new Table 2
Table 2 = 
SELECTCOLUMNS( 'Table 1'
             , "Column 1 Alias", [Column 1]
             )
The following gives all of Table 1 without "A" as new filter Table 3 as the result of FILTER is a table
Table 3 = 
SELECTCOLUMNS(  FILTER ( 'Table 1', 'Table 1'[Column 1] <> "A" )
             , "Column 1 Alias", [Column 1]
             )

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.