Once we are able to create virtual tables within PowerBI using Functions such as EVALUATE , CALCULATE TABLE etc it might be required to filter the contents of the virtual table based on certain Input criteria.
In such cases we can use the TREATAS function to apply filters to a Virtual or Actual Table as needed. In the below screenshot you can see how the TotalQTY only Contain the results for the year 2015.
This is achieved using the below DAX Formula
TotalQty = CALCULATE( SUM('Fact Order'[Quantity]),
TREATAS(
FILTER( SUMMARIZE('Fact Order', 'Dimension Date'[Calendar Year Label],'Dimension Date'[Calendar Month Label]),
'Dimension Date'[Calendar Year Label]="CY2015" ),
'Dimension Date'[Calendar Year Label],'Dimension Date'[Calendar Month Label])
)Here we first create a summary table based on year and month, we then proceed to filter the summary table only for data belonging to the year 2015, subsequently all year and month values from the filtered summary table are passed as filters to the Calculate Function in order to aggregate the Quantity.
The SQL Equivalent of the above query is
SELECT *
FROM Fact.[Order] o
INNER JOIN Dimension.DATE d ON o.[Order Date Key] = d.DATE
INNER JOIN (
-- TREAT AS VALUE starts here
SELECT d.[Calendar Year]
,[Calendar Month Number]
FROM Dimension.DATE d
INNER JOIN Fact.[Order] o ON d.DATE = o.[Order Date Key]
WHERE d.DATE BETWEEN cast(year('2013-01-01') AS VARCHAR(4)) + '-01-01'
AND cast('2013-05-01' AS DATE)
GROUP BY d.[Calendar Year]
,[Calendar Month Number]
) ta ON d.[Calendar Year] = ta.[Calendar Year]
AND d.[Calendar Month Number] = ta.[Calendar Month Number]Please Consider Subscribing
