Remove rows from a Join Table

[Anonymous]

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

[dedelman_clng · Accepted solution]

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

[dedelman_clng]

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

[dedelman_clng · Accepted solution]

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

[Anonymous]

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

Thanks for your tips!

Cheers!

[Anonymous]

@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

[Anonymous]

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

Cheers!

[Anonymous]

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 

[dedelman_clng · Accepted solution]

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