topic Re: Remove rows from a Join Table in DAX Commands and Tips

Remove rows from a Join Table

Anonymous — Wed, 28 Apr 2021 22:10:35 GMT

Dear community,

I'm trying to figure out how to exclude the rows for a specific ID, in a column of a Join table.

The table has this code:

Spot_FW = UNION(

SELECTCOLUMNS(UDM_DMO_FORWARD_VALOR,"ID_INDICE",UDM_DMO_FORWARD_VALOR[UDM_FORMULADOR_INDICES.ID],"Data_Cotacao",UDM_DMO_FORWARD_VALOR[DATA_COTACAO],"Cotacao",UDM_DMO_FORWARD_VALOR[VALOR],"ID_FW",UDM_DMO_FORWARD_VALOR[ID_FWD],"ID Spot","Not Spot","Prazo",UDM_DMO_FORWARD_VALOR[CONTRATO],"Validade",UDM_DMO_FORWARD_VALOR[ValidarContrato]),

(SELECTCOLUMNS(UDM_DMO_SPOT_VALOR,"ID_INDICE",UDM_DMO_SPOT_VALOR[UDM_FORMULADOR_INDICES.ID],"Data_Cotacao",UDM_DMO_SPOT_VALOR[DATA_COTACAO],"Cotacao",UDM_DMO_SPOT_VALOR[VALOR],"ID_FW","Not FW","ID Spot",UDM_DMO_SPOT_VALOR[ID_SPOT], "Prazo","Spot","Validade","0")))

I need help to remove from UDM_DMO_SPOT_VALOR table, "ID Spot",UDM_DMO_SPOT_VALOR[ID_SPOT], the ID=30 (i.e. UDM_DMO_SPOT_VALOR[ID_SPOT]<>30).

Any tip on this?

Thanks in advance.

Regards

Re: Remove rows from a Join Table

dedelman_clng — Thu, 29 Apr 2021 14:34:53 GMT

Hi Anonymous - you can use FILTER on the output of SELECTCOLUMNS to remove rows prior to the UNION happening.

FILTER ( SELECTCOLUMNS (...), [Filter Condition] )

You could also potentially use FILTER inside SELECTCOLUMNS by first filtering the base table, then choosing the columns

SELECTCOLUMNS ( FILTER ( [Table], [Filter Condition] ), ... )

Hope this helps

David

Re: Remove rows from a Join Table

Anonymous — Thu, 29 Apr 2021 17:12:29 GMT

Thanks dedelman_clng! It worked. I tryied Filter function before, but it was not properly written in the DAX code.

Cheers!

Re: Remove rows from a Join Table

Anonymous — Fri, 21 May 2021 16:09:38 GMT

Hi dedelman_clng! Hope you're fine.

One more question if you can help:

Instead of one ID that I need to filter, I need 2 or more ID to be out of the table, what do you think will be the best option?

I've tryed NOT [ID_FW] {xx,yy,zz}, but it didn't work.

Any tip on this?

Thanks in advance

Regards

Re: Remove rows from a Join Table

dedelman_clng — Fri, 21 May 2021 19:46:46 GMT

FILTER( TableName, NOT(TableName[ID_FW] IN {"xx", "yy", "zz"} ) )

should work (obviously if the IDs are numbers, don't need the quotes). If it isn't please share your error message or what behavior you are seeing.

Hope this helps,

David

Re: Remove rows from a Join Table

Anonymous — Fri, 21 May 2021 22:31:07 GMT

dedelman_clng thanks for your tip, first of all. I've tried this one (in blue):

Spot_FW = UNION(

(SELECTCOLUMNS(UDM_DMO_FORWARD_VALOR,"ID_INDICE",UDM_DMO_FORWARD_VALOR[UDM_FORMULADOR_INDICES.ID],"Data_Cotacao",UDM_DMO_FORWARD_VALOR[DATA_COTACAO],"Cotacao",UDM_DMO_FORWARD_VALOR[VALOR],"ID_FW",UDM_DMO_FORWARD_VALOR[ID_FWD],"ID Spot","Not Spot","Prazo",UDM_DMO_FORWARD_VALOR[CONTRATO],"Validade",UDM_DMO_FORWARD_VALOR[ValidarContrato]), filter(UDM_DMO_FORWARD_VALOR,NOT (UDM_DMO_FORWARD_VALOR[ID_FWD] IN { 34, 24, 62, 32})),

SELECTCOLUMNS(OMIE,"ID_INDICE",OMIE[ID_INDICE],"Data_Cotacao",OMIE[DATA_COTACAO],"Cotacao",OMIE[OMIE D+1],"ID_FW","Not FW","ID Spot",OMIE[ID_SPOT],"Prazo","Spot","Validade","0"),

filter(SELECTCOLUMNS(UDM_DMO_SPOT_VALOR,"ID_INDICE",UDM_DMO_SPOT_VALOR[UDM_FORMULADOR_INDICES.ID],"Data_Cotacao",UDM_DMO_SPOT_VALOR[DATA_COTACAO],"Cotacao",UDM_DMO_SPOT_VALOR[VALOR],"ID_FW","Not FW","ID Spot",UDM_DMO_SPOT_VALOR[ID_SPOT], "Prazo","Spot","Validade","0"),[ID Spot]<>30)))

Could you please give me any insight on this formula?

Thanks in advance

cheers

Re: Remove rows from a Join Table

dedelman_clng — Fri, 21 May 2021 22:50:43 GMT

Hi Anonymous - please reread my initial response to your post. You are not following the pattern I suggested. See https://dax.guide/filter/ , https://dax.guide/selectcolumns/ and https://dax.guide/union/ for proper syntax.

You should also use a tool like https://www.daxformatter.com/ to make your code readable. It will also tell you if your code has a syntax error (though it won't necessarily tell you what the error is). I can't get your code as written to compile on daxformatter so there are issues with it that need to be resolved.

Hope this helps

David

Re: Remove rows from a Join Table

Anonymous — Sat, 22 May 2021 19:22:12 GMT

dedelman_clng Yes, you're right. I have reordered the code and it worked.

Thanks for your tips!

Cheers!