cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RachnaV
Helper I
Helper I

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 :

Data For RankX query.JPG

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.

4 REPLIES 4
OwenAuger
Super User I
Super User I

Hi @RachnaV 

Interesting...I tested both your original code & my code in DAX studio, applying different overall filters, and they returned the same rankings (I used data from your original post).

 

The RankByOrderDate values in your screenshot above certianly look odd - are those the values in the column or some sort of aggregations?. How did you test the code? Did you materialize it as a calculated table in Power BI? 

 

My original code used ALLEXCEPT(...) as a means to keep just the filters resulting from context transition for the three columns whose filters you wanted to keep. It then also used KEEPFILTERS ( OnTimeTable ) to apply OnTimeTable as a filter but intersected with the existing filter context. It gave the correct result at my end but clear

 

In any case, having re-looked at my code, I would change things a bit.

Rather than using ALLEXCEPT to clear filters, this version instead saves the filters you want to keep in a variable ContinentCountryCategoryFilters, and applies this filter along with OnTimeTable to give the set of rows to perform the ranking over.

 

Does this work any better?

 

=
GENERATE (
    OnTimeTable,
    -- Save Continent/Country/Product Category for current row
    VAR ContinentCountryCategoryFilters =
        CALCULATETABLE (
            SUMMARIZE (
                OnTimeTable,
                OnTimeTable[Customer Continent],
                OnTimeTable[Customer Country],
                OnTimeTable[Product Category]
            )
        )
    VAR RankByOrderDate =
        RANKX (
            CALCULATETABLE (
                OnTimeTable,
                ContinentCountryCategoryFilters, -- Apply saved filters for current row
                OnTimeTable -- Restore original visible rows of OnTimeTable 
            ),
            OnTimeTable[Order Date],
            ,
            ASC
        )
    VAR RankByDeliveryDate =
        RANKX (
            CALCULATETABLE (
                OnTimeTable,
                ContinentCountryCategoryFilters, -- Apply saved filters for current row
                OnTimeTable -- Restore original visible rows of OnTimeTable 
            ),
            OnTimeTable[Delivery Date],
            ,
            ASC
        )
    VAR DeliveryAsPerQueueOrder =
        INT ( RankByOrderDate = RankByDeliveryDate )
    RETURN
        ROW (
            "RankByOrderDate", RankByOrderDate,
            "RankByDeliveryDate", RankByDeliveryDate,
            "DeliveryAsPerQueueOrder", DeliveryAsPerQueueOrder
        )
)

 Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

I tired this code but it gave an error - There's not enough memory to complete this operation. Please try again later when there may be more available memory.

OwenAuger
Super User I
Super User I

Hi @RachnaV 

First of all, as an overall structure for the table expression, I would suggest using GENERATE & ROW (similar to the method in this article).

 

In your case, GENERATE could take OnTimeTable as the first argument, then in the second argument store each of the values for the new columns in variables and combine with the ROW function.

 

I would also suggest a few other tweaks to your original code, in particular changing FILTER( OnTimeTable,...) to ALLEXCEPT(...) & KEEPFILTERS(...) as shown below.

 

I also made one other change, so that the ranks are calculated over distinct dates, rather than the entire table. However, I realise that this could give different results when there are duplicate date values, so you may want to change this back where I have indicated in the comments.

 

=
GENERATE (
    OnTimeTable,
    VAR RankByOrderDate =
        RANKX (
            CALCULATETABLE (
                VALUES ( OnTimeTable[Order Date] ), // Could change back to OnTimeTable
                ALLEXCEPT (
                    OnTimeTable,
                    OnTimeTable[Customer Continent],
                    OnTimeTable[Customer Country],
                    OnTimeTable[Product Category]
                ),
                KEEPFILTERS ( OnTimeTable )
            ),
            OnTimeTable[Order Date],
            ,
            ASC
        )
    VAR RankByDeliveryDate =
        RANKX (
            CALCULATETABLE (
                VALUES ( OnTimeTable[Delivery Date] ), // Could change back to OnTimeTable
                ALLEXCEPT (
                    OnTimeTable,
                    OnTimeTable[Customer Continent],
                    OnTimeTable[Customer Country],
                    OnTimeTable[Product Category]
                ),
                KEEPFILTERS ( OnTimeTable )
            ),
            OnTimeTable[Delivery Date],
            ,
            ASC
        )
    VAR DeliveryAsPerQueueOrder =
        INT ( RankByOrderDate = RankByDeliveryDate )
    RETURN
        ROW (
            "RankByOrderDate", RankByOrderDate,
            "RankByDeliveryDate", RankByDeliveryDate,
            "DeliveryAsPerQueueOrder", DeliveryAsPerQueueOrder
        )
)

 

Hopefully this is of some use!

 

Regards,

Owen


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

Hi Owen,

 

Thanks for the response on my query. Using the code given above, it is not giving the correct ranking(even after replacing VALUES ( OnTimeTable[Delivery Date] ) with OnTimeTable) . Expected behaviour is that ranking should occur within a group of Continent, Country and Product Category. But see the snapshot of rankings for Asia ->Armenia -> Product Categories.

In this case the expected ranking for Cameras and Camcorders should be 1 but it is ranking it as 6. 

Similarly in Computers category the order against date 18/12/2009 should be ranked 1,  the order against date 20/12/2009 should be ranked as 2 and one with date 22/12/2009 should be ranked as 3. 

 

Ranking_Issue.gif

 

Not sure what is how the data is being ranked here. Would you please explain the use of AllExcept and KeepFilters in your code above. This table has been generated using the code that you shared, only change being with VALUES part.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps