Summarize and filter in two tables

[dapperscavenger]

Hello everyone

I have the following formula:

  VAR _Table = SUMMARIZE( 
	'Fact APO', [L8 SKU],
	"_FTBPEU", SUM('Fact APO'[FTBP EU])
    )

RETURN

COUNTROWS(
	FILTER(_Table, 	([_FTBPEU] <> 0) 
 ))

I want to add a filte to it. The filter is

DimL8[Status]= "ZD"

The two tables are connected by the SKU field

Done APO[L8 SKU] (many) ------ DimL8[SKU] (one)

But I can't seem to make this filter work. The purpose of the formula is to show the number of SKUs in the ZD state where the forecast (FTBP) is not zero. The reason is that we shouldn't have SKUs in ZD with forecast, so this formula will act as a kind of alert and a measure of how clean our forecast is.

The Fact APO table should be summarized because there are SKUs with multiple rows, for example, where they go to more than one customer. I want a different count regardless of how many customers

Thank you

[AlexisOlson · Accepted solution]

You could add the dimension condition as a filter when building your summary _Table.

VAR _Table =
    ADDCOLUMNS (
        SUMMARIZE ( 'Fact APO', 'Fact APO'[L8 SKU] ),
        "_FTBPEU",
            CALCULATE (
                SUM ( 'Fact APO'[FTBP EU] ),
                DimL8[Status] = "ZD"
            )
    )
RETURN
    COUNTROWS (
        FILTER ( _Table, [_FTBPEU] <> 0 )
    )