Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
BalazsNy
Helper I
Helper I

How to compare & get the lowest value of 2 columns in different table?

Hi!

I have 2 tables including Product ID / Product / Sold date (1 / 2). These tables have 1:1 relationship based on Product ID. I would like to build up a table visual including Product ID / Product / First sold date (comparing sold date of Table1 & Table2).

I would like to get the below result (TableOveral):

BalazsNy_0-1697229642897.png

Could you please advise a DAX formula to solve this?

Thanks in advance!

1 ACCEPTED SOLUTION

pls try this

Measure = 
VAR _date1 = MAX('Table1'[Sold datel])
VAR _date2 = MAX('Table2'[Sold date_2])
RETURN
SWITCH( TRUE(),
    ISBLANK(_date1) ,_date2,
    ISBLANK(_date2), _date1 ,
MINX({MIN('Table1'[Sold datel]),MIN('Table2'[Sold date_2])},[Value]))

Screenshot_4.png

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Enausre that the 3 columns are spelled the same way in both tables.  In the Query Editor, append the 2 tables.  Create your visual from the appended table.  Write this measure

First date = min(appendedtable[Date])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Didn't @Ahmedx solve this three hours earlier?

Ahmedx
Super User
Super User

or you can wirt this

Measure = MIN(MIN('Table1'[Sold datel]),MIN('Table2'[Sold date_2]))

Thanks. It is almost perfect, but there are some rows when only one of the tables contains sold date, in the other table it is emtpy (so its value is 0). In that case, overall table should contain the existing date, not 0.

pls try this

Measure = 
VAR _date1 = MAX('Table1'[Sold datel])
VAR _date2 = MAX('Table2'[Sold date_2])
RETURN
SWITCH( TRUE(),
    ISBLANK(_date1) ,_date2,
    ISBLANK(_date2), _date1 ,
MINX({MIN('Table1'[Sold datel]),MIN('Table2'[Sold date_2])},[Value]))

Screenshot_4.png

Thanks, but finally a managed to solve it with the following calculated column in Table1:

First_sold_date =
VAR date1 = Table1[Sold_date_1]
VAR date2 = RELATED(Table2[Sold_date_2])
RETURN
if(
    ISBLANK(date1)=ISBLANK(date2),
    MIN(date1,date2),
    MAX(date1,date2)
    )
Ahmedx
Super User
Super User

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RixkU7pqZfn8MqTEW?e=sgdCFM

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.