topic Re: Filter table before using CROSSJOIN in DAX Commands and Tips

Filter table before using CROSSJOIN

Anonymous — Fri, 12 Feb 2021 14:34:24 GMT

Hi,

I want to create a crossjoin table of the table below with only "A" values in the "field" column. I know how to do this if I first manually filter the field. However, this is not a suitable option for me because I want to minimize the amount of tables.

This is the dax formula I would use after manual filteration:

crossjoin = VAR _tab = ADDCOLUMNS( CROSSJOIN(ALL('table where field filtered'[id]), ALL('table where field filtered'[category])), "idCategory", 'table where field filtered'[id] & 'table where field filtered'[category] ) RETURN ADDCOLUMNS( _tab, "isyes", VAR _val = LOOKUPVALUE('table where field filtered'[value], 'table where field filtered'[id_category], [idCategory]) RETURN IF(_val = BLANK(), 0, _val) )

So my questions are:

1. Is it possible to filter the "value" column in the dax formula above?

2. Is there a way to a way to do the whole process in a measure instead of creating a new table?

You can find the pbix here.

I'm very grateful for everyone who have the time to help 🙂

Juls

Re: Filter table before using CROSSJOIN

amitchandak — Fri, 12 Feb 2021 16:01:59 GMT

Anonymous , what is the expected output table, is not very clear. Please share an example

Re: Filter table before using CROSSJOIN

Anonymous — Sun, 14 Feb 2021 16:26:21 GMT

amitchandak

Here's the expected output table. In this example I have filtered "field" A in query editor before creating the new table using crossjoin. My goal is to find a way to keep the original table untouched and do the "field" filtration in dax when creating the new crossjoined table.