SUMMARIZECOLUMNS returns blank values

[raymondchen]

I have a PowerBI dataset with 4 tables. Table 1 contains agent/company information; Table 2 contains product information; Table 3 contains value information, table 4 contains dates information. There are relationships between four tables. However, they are all direct queries. The details cannot be shown in the table view and I cannot access to power query for those four tables neither. I am trying to create a calculated table to pull all the data points that I care into one. I use the below DAX to create the table. However, I notice that it returns many rows with blank or 0 values. I am wondering if there is a way I can modify the DAX to have it return only the value is non blank or greater than 0.

NewTable =  
VAR x = YEAR(TODAY())*100 + MONTH(TODAY()) – 1
//get the month id as the most recent one
VAR y = FILTER(‘TABLE 1’, LEFT(‘TABLE 1’[ID], 1) = “A”)
//get the agent id starts with A
VAR z = FILTER(‘TABLE 1’, ‘TABLE 1’[COMPANY NAME] <> “-”)
//get the company name is not “-“
VAR n = FILTER(‘TABLE 2’, ‘TABLE 2’[PRODUCT TYPE] = “ABC” || ‘TABLE 2’[PRODUCT TYPE] = “BCD”)
//get only the product type is “ABC” or “BCD”
RETURN
SUMMARIZECOLUMNS(‘TABLE 1’[ID], ‘TABLE 1’[COMPANY NAME], ‘TABLE 2’[PRODUCT TYPE], y, z, n, “MAXMONTH”, x, “VALUE”, CALCULATE(SUM(‘TABLE 3’[VALUES]), FILTER(‘TABLE 4’[MONTH ID] = x)))

What the DAX returns

What I hope it will return

[AlexisOlson · Accepted solution]

First thing I'd try would be IGNORE around the x column.

NewTable =
VAR x = YEAR ( TODAY () ) * 100 + MONTH ( TODAY () ) - 1
VAR y = FILTER ( 'TABLE 1', LEFT ( 'TABLE 1'[ID], 1 ) = "A" )
VAR z = FILTER ( 'TABLE 1', 'TABLE 1'[COMPANY NAME] <> "-" )
VAR n =
    FILTER (
        'TABLE 2',
        'TABLE 2'[PRODUCT TYPE] = "ABC"
            || 'TABLE 2'[PRODUCT TYPE] = "BCD"
    )
RETURN
    SUMMARIZECOLUMNS (
        'TABLE 1'[ID],
        'TABLE 1'[COMPANY NAME],
        'TABLE 2'[PRODUCT TYPE],
        y,
        z,
        n,
        "MAXMONTH", IGNORE ( x ),
        "VALUE",
            CALCULATE (
                SUM ( 'TABLE 3'[VALUES] ),
                FILTER ( 'TABLE 4', 'TABLE 4'[MONTH ID] = x )
            )
    )

[raymondchen]

It works. Amazing! Thank you very much.