powerbidaxpowerbi-desktopdaxstudio

How to add new filter to a DAX query


I've that query for my previous question and I'm trying to show records if Table4 name = "post";

I've tried to make it work like that:

var posts = 
    FILTER(
    Table4, 
    Table4[name] = "post"
)

return SUMX(
    FILTER(
        posts,
        COUNTROWS(
            FILTER(
                RELATEDTABLE(Table2),
                COUNTROWS(
                    FILTER(
                        RELATEDTABLE(Table3),
                        Table3[table3Id] = Table4[table4Id] 
                    )
                ) > 0
            )
        ) > 0
    ), Table4[value])

And by adding && in filters that already are here like that:

SUMX(
    FILTER(
        Table4,
        COUNTROWS(
            FILTER(
                RELATEDTABLE(Table2),
                COUNTROWS(
                    FILTER(
                        RELATEDTABLE(Table3),
                        Table3[table3Id] = Table4[table3Id] 
                    )
                ) > 0
            )
        ) > 0  && Table4[name]="post"
    ), Table4[value])

But nothing works. I get blank table..


Solution

  • Try the following :

    SumValueForTable1Record = 
    VAR ValidTable4 =
        FILTER(
            Table4,
            Table4[name] = "post"
        )
    
    VAR ValidRecords =
        FILTER(
            ValidTable4,
            COUNTROWS(
                FILTER(
                    RELATEDTABLE(Table2),
                    COUNTROWS(
                        FILTER(
                            RELATEDTABLE(Table3),
                            Table3[table3Id] = EARLIER(Table4[table4Id])
                        )
                    ) > 0
                )
            ) > 0
        )
    
    RETURN
    SUMX(ValidRecords, Table4[value])