Forum Discussion

zamurr's avatar
zamurr
Microsoft Employee
10 years ago

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    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])
    • zamurr's avatar
      zamurr
      Microsoft Employee
      10 years ago

      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.

       

       

      • v-micsh-msft's avatar
        v-micsh-msft
        Microsoft Employee
        9 years ago

        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