CALCULATE function (DAX) - DAX | Microsoft Learn I am to author a report that has a few tables in the model with relationships intact. This article describes its internal behavior, and provides guidance on how to use it. . In other words, we are simulating the scenario of a large dimension by using the smallest possible data model. I already tried some options suggested in this forum like the ones appointed by@amitchandakin this previous posthttps://community.powerbi.com/t5/Desktop/Filter-data-based-on-multiple-criteria-in-same-column/m-p/2,but for some reason, my DAX doesn't work. By downloading the file(s) you are agreeing to our Privacy Policy and accepting our use of cookies. I hope this is helpful. The following Sales table measure definition produces a ratio of sales over sales for all sales channels. You cannot create a physical relationship between Date and Advertising, because the granularity is defined by two columns (Year and Month Number), and there are multiple rows with the same combination of year and month in the Date table. I'm trying to create a measure You can try something like this: 1. You have to use the measure instead of your revenue column to get the desired result. Always use a physical relationship to propagate filters whenever possible. Read more in Introducing SUMMARIZECOLUMNS. Using a table filter, you inherit the filter argument existing for the Product table, so you will not include a product Red or of the Contoso brand if it was not present in the existing filter. All rights reserved. Get BI news and original content in your inbox every 2 weeks! What is more important, you will not override the existing filter on such a column. You can optimize this by filtering only the two colors and two countries upfront, so the CROSSJOIN only materializes four combinations, but the entire process is removed by using TREATAS, which creates an arbitrary filter that is pushed to the storage engine in a direct way, without having to materialize a table in advance. In this article. What is Wario dropping at the end of Super Mario Land 2 and why? That means all conditions must be TRUE at the same time. How to Specify Multiple Filter Conditions in CALCULATE [Power BI] CROSSFILTER (