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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors