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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors