cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DXF_83
Helper III
Helper III

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
Super User II
Super User II

Hi @DXF_83 - 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




Proud to be a Super User!




View solution in original post

dedelman_clng
Super User II
Super User II

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




Proud to be a Super User!




View solution in original post

7 REPLIES 7
dedelman_clng
Super User II
Super User II

Hi @DXF_83  - 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




Proud to be a Super User!




dedelman_clng
Super User II
Super User II

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




Proud to be a Super User!




View solution in original post

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

Thanks for your tips!

Cheers!

@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
DXF_83
Helper III
Helper III

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 

dedelman_clng
Super User II
Super User II

Hi @DXF_83 - 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




Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.