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:
Units Sold = SUM(tbl_Sales[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. If you wish, please share your genuine Feedback or comments only related to this Blog Posts. It is my humble request that, please do not post any Spam comments or Advertising kind of comments, which will be Ignored.