Q1: What is row context transition?
Calculation transforms any existing row context into a filter context, or when the row value becomes a column's filter.
Q2: Why do we need a row context transition?
Because Row Context does NOT respect the relationship, In most cases we need to have a relationship to get data from different tables. even if we use a related and related table.
Q3: What triggers the row context transition?
We need to have two conditions: 1) have a row context; and 2) have CALCULATE
Q4: Where is row context involved?
There are two cases:
Case 1: Calculated Columns
Fact sales table, you want to have a product subcategory, or product category, or any classifications
Dimension customer table, you want to have the sales amount
Case 2: All iterators
FILTER DAX Function (Aggregation)
Q5: What is an explicit row context transition?
CALCULATE is presented in DAX script.
Q6: What is an implicit row context transition?
Where you don't see CALCULATE, However, a measure is used, such as [SalesAmount]
Q7: What problems can happen when there are duplicated records?
especially for fact tables, such as sales, where sales records are not unique, we will get the wrong summary, as the filtered result is duplicated.
Q8: Any samples?
CALCULATE (
COUNTROWS (
FILTER (
Customer,
CALCULATE (
ISEMPTY ( Sales )
)
)
),
Customer[Total Children] > 0,
'Product'[Category] = "Games and Toys"
)
[Measure2] =
VAR CustomersWithTwoProducts =
FILTER (
Customer,
CALCULATE (
DISTINCTCOUNT ( Sales[ProductKey] )
) >= 2
)
VAR Result =
COUNTROWS ( CustomersWithTwoProducts )
RETURN
Result
[Measure3] =
VAR NumProductsSelection =
SELECTEDVALUE (
'Num of Products'[Num Products],
2
)
VAR CustomersWithSelectedProducts =
FILTER (
Customer,
CALCULATE (
DISTINCTCOUNT ( Sales[ProductKey] )
) >= NumProductsSelection
)
VAR Result =
COUNTROWS ( CustomersWithSelectedProducts )
RETURN
Result
[Measure4] =
VAR Threshold = [Threshold]
VAR MaxSpikes =
FILTER (
VALUES ( 'Date'[Year Month] ),
VAR SalesCurrentMonth = [Sales Amount]
VAR SalesPreviousMonth =
CALCULATE (
[Sales Amount],
PREVIOUSMONTH ( 'Date'[Date] )
)
VAR EnteringThreshold = SalesCurrentMonth >= Threshold
&& SalesPreviousMonth < Threshold
RETURN
EnteringThreshold
)
VAR Result =
COUNTROWS ( MaxSpikes )
RETURN
Result
[Measure5] =
IF (
ISINSCOPE ( Customer[Name] ),
RANKX (
ALLSELECTED ( Customer ),
[Margin]
)
)
[Measure6] =
SUMX (
VALUES ( Balances[Name] ),
[Last Bal NB]
)
IF (
HASONEVALUE ( 'Target Currency'[Currency] ),
VAR AggregatedSalesInCurrency =
ADDCOLUMNS (
SUMMARIZE (
Sales,
'Date'[Calendar Year Month],
'Source Currency'[Currency]
),
"@SalesAmount", [Sales (Internal)],
"@Rate",
CALCULATE (
SELECTEDVALUE ( 'Monthly Exchange Rates'[Rate] )
)
)
VAR Result =
SUMX (
AggregatedSalesInCurrency,
IF (
NOT (
ISBLANK ( [@Rate] )
),
[@SalesAmount] * [@Rate],
ERROR ( "Missing conversion rate" )
)
)
RETURN
Result
)
[Measure8] =
IF (
HASONEVALUE ( 'Date'[Calendar Year] ),
VAR CustomersInSegment =
FILTER (
ALLSELECTED ( Customer ),
VAR SalesOfCustomer = [Sales Amount]
VAR SegmentForCustomer =
FILTER (
'Customer Segments',
NOT ISBLANK ( SalesOfCustomer )
&& 'Customer Segments'[Min Sales] < SalesOfCustomer
&& 'Customer Segments'[Max Sales] >= SalesOfCustomer
)
VAR IsCustomerInSegments =
NOT ISEMPTY ( SegmentForCustomer )
RETURN
IsCustomerInSegments
)
VAR Result =
CALCULATE (
COUNTROWS ( Customer ),
KEEPFILTERS ( CustomersInSegment )
)
RETURN
Result
)
No comments:
Post a Comment