Filter table before using CROSSJOIN

[Anonymous]

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

[amitchandak]

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

[Anonymous]

@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.