Compare two columns of a table and create a derived column based on comparison in DAX Measure
I need to compare two columns of a table (not a physical table but created in a DAX measure) and derive another column based on the comparison result. Can some one help with how this can be achieved within a DAX measure. My data is in below format : But above table is not a physical table but a table created in DAX measure using below code : = ADDCOLUMNS ( 'OnTimeTable', "RankByOrderDate", RANKX ( CALCULATETABLE ( 'OnTimeTable', FILTER ( 'OnTimeTable', 'OnTimeTable'[Customer Continent] = EARLIER ( 'OnTimeTable'[Customer Continent] ) && 'OnTimeTable'[Customer Country] = EARLIER ( 'OnTimeTable'[Customer Country] ) && 'OnTimeTable'[Product Category] = EARLIER ( 'OnTimeTable'[Product Category] ) ) ), 'OnTimeTable'[Order Date], , ASC ), "RankByDeliveryDate", RANKX ( CALCULATETABLE ( 'OnTimeTable', FILTER ( 'OnTimeTable', 'OnTimeTable'[Customer Continent] = EARLIER ( 'OnTimeTable'[Customer Continent] ) && 'OnTimeTable'[Customer Country] = EARLIER ( 'OnTimeTable'[Customer Country] ) && 'OnTimeTable'[Product Category] = EARLIER ( 'OnTimeTable'[Product Category] ) ) ), 'OnTimeTable'[Delivery Date], , ASC ) ) Both the RankByOrderDate and RankByDeliveryDate columns are created by Ranking the table data based on Order and Delivery Dates respectively, within a group of Continent,Country and Product Category. Comparing these two rank columns, a new column[DeliveryAsPerQueueOrder, shown in sample data] needs to be created with the below logic : If the two columns being compared are equal then assign 1 else 0. Finally I will need to SUM the value of this derived column (this part is later first focus is on getting the column with desired values). All this functionality needs to be done in a DAX measure, without using a calculated column or calculated table.
