Forum Discussion
Remove rows from a Join Table
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:
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
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
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
7 Replies
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
Thanks dedelman_clng! It worked. I tryied Filter function before, but it was not properly written in the DAX code.
Cheers!
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
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
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 advancecheersdedelman_clng Yes, you're right. I have reordered the code and it worked.
Thanks for your tips!
Cheers!
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