DAX Query Tabular: How to use SUMMARIZE Function with FILTER Function, which have different columns on both functions

Question

We are Using SELECTCOLUMNS Function for providing Alais Name, SUMMARIZE function for Aggregating the Data, also we are using FILTER function to filter data in the same query.

Requirement : We want to perform Aggregation on Some other Columns and want to perform Filter on another column in a same query. As we can do in SQL, Can we do in DAX also, if yes then can you post an example?

SQL Example:

select Column1 from TableName where Column2 = 'PO Flag' group by Column1.

DAX Example:

Evaluate (    SelectColumns(     Filter (      Summarize ( 
     'Internet Sales', 
     'Product'[Model Name], 
     "Sales",SUM('Internet Sales'[Sales Amount])
     ) ,  
     'Product'[Product Name]="HL Road Tire"
     )  ,    "Product",'Product'[Model Name],    "Sales",[Sales]   )   )

Please help !!!!

score 0 · Answer 1 · answered Oct 09 '17 at 12:22

It could work something like this:

EVALUATE (    
    FILTER(
        SELECTCOLUMNS (     
            SUMMARIZE (
                'Internet Sales', 
                'Product'[Model Name], 
                "Sales", 
                CALCULATE(SUM('Internet Sales'[Sales Amount]), 'Product'[Product Name] = "HL Road Tire")), 
            "Product", [Model Name],
            "Sales", [Sales]),
        NOT (ISBLANK([Profit]))))

So, in this case I used the inner CALCULATE() to apply the filter on Product[Product Name]. You were trying to filter out the rows after they were already summarized, but because the column that was being filtered didn't exist in the summarized table it didn't work.

DAX Query Tabular: How to use SUMMARIZE Function with FILTER Function, which have different columns on both functions

1 Answers1