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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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:

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

2 ACCEPTED SOLUTIONS
dedelman_clng
Community Champion
Community Champion

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

View solution in original post

dedelman_clng
Community Champion
Community Champion

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

View solution in original post

7 REPLIES 7
dedelman_clng
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Not applicable

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

Thanks for your tips!

Cheers!

Anonymous
Not applicable

@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
Not applicable

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

Cheers!

Anonymous
Not applicable

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
Community Champion
Community Champion

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.