Scenario:
Suppose we have a Data Model as per below:
Based on the above Model, the No. of Units Sold by Month-Year and Country is as follows:
Based on the above Model, the No. of Units Sold by Month-Year and Country is as follows:
Units Sold = SUM(tbl_Sales[Units_Sold])
Now we look at how to pass the below Filters using various methods to calculate the Units Sold.
DimCalendar[MonthYear] IN {"Jan-2022","Feb-2022","Mar-2022"}
tbl_Region[Country_Name] IN {"France", "Japan"}
The below 2 Methods, will ignore ALL (external) Filters from the DimCalendar and tbl_Region tables, and apply the implicit filters passed with in the expression:
Frn_Jpn Q1 Sales (Filter ALL Method1) =
CALCULATE( [Units Sold],
FILTER( ALL(DimCalendar) ,
DimCalendar[MonthYear] IN {"Jan-2022","Feb-2022","Mar-2022"}
),
FILTER( ALL(tbl_Region) , tbl_Region[Country_Name] IN {"France", "Japan"})
)
Frn_Jpn Q1 Sales (Filter ALL Method2) =
CALCULATE( [Units Sold],
DimCalendar[MonthYear] IN {"Jan-2022","Feb-2022","Mar-2022"},
tbl_Region[Country_Name] IN {"France", "Japan"}
)
The below Methods, impact by (external) Filters from any Slicers used from the Data model:
Frn_Jpn Q1 Sales (Filter Method) =
CALCULATE( [Units Sold],
FILTER(DimCalendar,
DimCalendar[MonthYear] IN {"Jan-2022","Feb-2022","Mar-2022"}
),
FILTER(tbl_Region,
tbl_Region[Country_Name] IN {"France", "Japan"}
)
)
Frn_Jpn Q1 Sales (Values Method) =
CALCULATE( [Units Sold],
FILTER(VALUES(DimCalendar[MonthYear]),
DimCalendar[MonthYear] IN {"Jan-2022","Feb-2022","Mar-2022"}
),
FILTER(VALUES(tbl_Region[Country_Name]),
tbl_Region[Country_Name] IN {"France", "Japan"}
)
)
Frn_Jpn Q1 Sales (TreatAs Method) =
CALCULATE( [Units Sold],
KEEPFILTERS(
TREATAS(
{ ("Q1-2022", "France"),
("Q1-2022", "Japan")
},
DimCalendar[Qtr_Yr],
tbl_Region[Country_Name]
)
)
)
Frn_Jpn Q1 Sales (Tuples Method) =
CALCULATE( [Units Sold],
KEEPFILTERS(
( tbl_Region[Region_Name],tbl_Region[Country_Name] )
IN { ("Europe","France"),("Asia","Japan")}
),
KEEPFILTERS(
( DimCalendar[MonthYear] )
IN { ("Jan-2022"),("Feb-2022"),("Mar-2022") }
)
)
Result:
Notes:
Please make the required adjustments to the Filter Context in the expressions based on your Scenario and Data Model.
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.