@powerbiuser9999 Claro, aquí está con comentarios:
Measure =
// Get a distinct single column table of customers that have bought Instore
// Since we need to filter the table for Instore, we need to use SELECTCOLUMNS
// to return a single column from the FILTER statement and then apply DISTINCT
// to only get the unique values. We set this table variable aside until the end
VAR __Table = DISTINCT(SELECTCOLUMNS(FILTER('Table',[Type of Shop] = "Instore"),"Customer ID",[Customer ID]))
// This is the tricky one. We start with a distinct list of customers and then add
// two columns that use the iterator function, MAXX to return the highest rank for
// Instore and Online. The columns are called "Instore" and "Online" and the only
// difference is the filter for Type of Shop. Each column calculation starts by
// getting the "current" value of [Customer ID] for the row and then uses that as
// part of the filter clause to ensure we are getting the rank for that customer.
VAR __Table1 =
ADDCOLUMNS(
// This is the start of the table.
DISTINCT('Table'[Customer ID]),
// Adding Instore column.
"Instore",
// This is the calculation for the Instore column, nested VAR and RETURN
VAR __Customer = [Customer ID]
RETURN
MAXX(FILTER('Table',[Customer ID] = __Customer && [Type of Shop] = "Instore"),[Rank of shops]),
// Adding Online column
"Online",
// This is the calculation for the Online column, nested VAR and RETURN
VAR __Customer = [Customer ID]
RETURN
MAXX(FILTER('Table',[Customer ID] = __Customer && [Type of Shop] = "Online"),[Rank of shops])
)
// Now we use Table1 to create a new table var that simply filters our __Table1 where
// the Online rank value is greater than the Instore rank value. We also only want the
// customer IDs so we use SELECTCOLUMNS again to just grab that column.
VAR __Table2 = SELECTCOLUMNS(FILTER(__Table1,[Online] > [Instore]),"Customer ID",[Customer ID])
RETURN
// We now have two table vars, __Table and __Table2 that each have single column of
// unique customer values. __Table lists customer that have bought Instore and __Table2
// has a list of customers that have an Online rank that is higher than the Instore rank
// so, since we want customers with an Online rank higher than Instore rank that have
// also bought Instore, then we want the INTERSECT of the two tables (basically the values
// that exist in both tables). We then simply use COUNTROWS to count the number of rows
// in the intersection of the two tables.
COUNTROWS(INTERSECT(__Table2, __Table))
