Hi ​@Leslie Lei ,

I would try this filter:

FILTER (CASE WHEN BIND_FILTERS(“Table B”,“Table A”. “Procurement type” != ‘Own produced’,“Table C”.”Transportation” NOT IN (‘Airline’, ‘Railway’))

THEN “Table B”.”Material code”

ELSE NULL ) IS NOT NULL;

or maybe a drop down filter component with the following dimension:

CASE WHEN BIND_FILTERS ( “Table B”,“Table A”. “Procurement type” != ‘Own produced’,“Table C”.”Transportation” NOT IN (‘Airline’, ‘Railway’)) THEN 'Materials I wanna show' ELSE 'Other materials' END

I hope that helps!

Joao

Hi ​@Leslie Lei 

Regarding your last comment 

Besides, I would be very appreciative if you can share your idea of visualizing “Material code”, “Procurement type” and “Transportation” in the same table.

You might wanna try DOMAIN_TABLE() combined with BIND() functions.

I would try something like this:

PU_FIRST(DOMAIN_TABLE( "Table B"."Material code"),  "Table B"."Material code", BIND("Table B",

“Table A”. “Procurement type”), BIND("Table B", “Table C”.”Transportation”)

DOMAIN_TABLE() requires you to use a PU function, so in this example I used PU_FIRST.

Maybe you can try to use the two BIND() functions within the domain table to create a row for each combination of the material code and procurement type and transportation - I haven’t tested it like that though.

Hope that helps!

Joao

FILTER (CASE WHEN BIND_FILTERS(“Table B”,“Table A”. “Procurement type” != ‘Own produced’,“Table C”.”Transportation” NOT IN (‘Airline’, ‘Railway’))

CASE WHEN BIND_FILTERS ( “Table B”,“Table A”. “Procurement type” != ‘Own produced’,“Table C”.”Transportation” NOT IN (‘Airline’, ‘Railway’)) THEN 'Materials I wanna show' ELSE 'Other materials' END

Hi ​@joaocarlos.macie 

I appreciate your time a lot! I tried the filter below but unfortunatly it has syntax errors: 

FILTER (CASE WHEN BIND_FILTERS(“Table B”,“Table A”. “Procurement type” != ‘Own produced’,“Table C”.”Transportation” NOT IN (‘Airline’, ‘Railway’))

THEN “Table B”.”Material code”

ELSE NULL ) IS NOT NUL

The good news is that the second code written by you works well, though I used it to create a column in a table, not a filter dropdown:

CASE WHEN BIND_FILTERS ( “Table B”,“Table A”. “Procurement type” != ‘Own produced’,“Table C”.”Transportation” NOT IN (‘Airline’, ‘Railway’)) THEN 'Materials I wanna show' ELSE 'Other materials' END

However I haven’t though of a way of populating both fields “Procurement type” and “Transportation”  from Table A and C in this new table. More specifically, I can only populate either “Procurement type” or “Transportation”. It seems that PU plus BIIND functions only work in 1 to n to 1 data models, if I’m not mistaken.

Anyhow thank you so much for your help! If you have any new ideas, pls let me know.

Br,

Leslie Lei

Hi ​@Leslie Lei 

Glad to know that the code worked in the table!

For the filter, I just realized that the CASE WHEN is missing an “END” in the code I sent you. So that should fix the issue if you need to use it as a filter.

For the second topic, you just helped me remember why I’ve never implemented such scenario! haha I don’t think I ever managed to represent that in a single table in the format you would like to.

However, one thing that came to my mind is that you could use the PU_STRING_AGG() to represent that. It won’t be in the original format you expected, but it might be a nice workaround.

You can create a table with 3 dimensions:

• Table B.Material Code
• PU_STRING_AGG ( "Table B" ,"Table A"."Procurement type" , ' - ' )
• PU_STRING_AGG ( "Table B" ,"Table C"."Transportation" , ' - ' )

Let me know if that helps!

Cheers,

Joao

Hi Joao,

Your solution indeed works. Based on this table, I created some filters to filter for different warehouses. Take UK as an example:

FILTER BIND_FILTERS (Table B, PU_STRING_AGG(Table B, TableA.Warehouse, ‘/’) = ‘UK’, PU_STRING_AGG (TableB, TableC.Distribution Center, ‘/’) = ‘UK’)

Hopefully this will be helpful for your work too!

Kind regards,

Leslie