topic Re: Min Date from three unrelated tables in DAX Commands and Tips

Min Date from three unrelated tables

Tmk123 — Wed, 04 Sep 2024 15:20:25 GMT

Hello,

I am creating a calculated column and it's returning blanks. Is there a good way to manage this? Perhaps the statement under the "Return" can be altered? The three tables are not related. Thank you.

_Create Date =

VAR _itemNo = Table1[ItemNo]

VAR _Date_InvMaster =

CALCULATE( MIN(Table1[record_est_date]), FILTER(Table1, Table1[item_num] = _itemNo))

VAR _Date_HockPO =

CALCULATE( MIN('Table2'[received_date]), FILTER('Table2', 'Table2'[item_num] = _itemNo))

VAR _Date_Inventory =

CALCULATE( MIN(Table3[LastReceipt]), FILTER(Table3, Table3[ItemNo] = _itemNo))

RETURN

MIN(MIN(_Date_InvMaster, _Date_HockPO), _Date_InvMaster)

Below is the result. The item number is on the left. The three numbered columns show the VAR outputs above. The last column is the output of the calculated column. My goal is to have the minimum date from the three VARs appear in the calculated column.

Re: Min Date from three unrelated tables

Gabry — Wed, 04 Sep 2024 15:41:27 GMT

Hello,

I think one problem is that you called 2 times the same variable date_invmaster.

MIN(MIN(_Date_InvMaster, _Date_HockPO), _Date_InvMaster)

The second problem I think it's that you are not excluding blank values. So the min is always the blank.

You need to exclude blanks

Re: Min Date from three unrelated tables

mark_endicott — Wed, 04 Sep 2024 16:03:44 GMT

Tmk123 You need to exclude blanks from the calculations, otherwise MIN will find that. You can also improve the RETURN statement to use MINX which will iterate over a VAR that unions all the results together, you can try amending the DAX below to suit your model:

VAR _itemNo = 'Table'[item_num] VAR _Date_InvMaster = CALCULATETABLE ( SUMMARIZE ( 'Table', 'Table'[item_num], "Date", MIN ( 'Table'[record_est_date] ) ), 'Table'[item_num] = _itemNo, 'Table'[record_est_date] <> BLANK () ) VAR _Date_HockPO = CALCULATETABLE ( SUMMARIZE ( Table2, Table2[item_num], "Date", MIN ( Table2[received_date] ) ), 'Table2'[item_num] = _itemNo, Table2[received_date] <> BLANK () ) VAR _Date_Inventory = CALCULATETABLE ( SUMMARIZE ( Table3, Table3[item_num], "Date", MIN ( Table3[LastReceipt] ) ), Table3[item_num] = _itemNo, Table3[LastReceipt] <> BLANK () ) VAR _union_tables = UNION ( _Date_InvMaster, _Date_HockPO, _Date_Inventory ) RETURN MINX ( _union_tables, [Date] )

Re: Min Date from three unrelated tables

Tmk123 — Thu, 05 Sep 2024 12:38:29 GMT

So right! I did call it twice. Thank you!

Re: Min Date from three unrelated tables

mark_endicott — Thu, 05 Sep 2024 13:26:12 GMT

Tmk123 - Have you been able to check if this solution works fr you?

Re: Min Date from three unrelated tables

Tmk123 — Thu, 05 Sep 2024 14:31:25 GMT

Thanks for your response. I am in the middle of trying your suggestion. It's giving me a circular dependency error so I'm researching to see how to overcome that - that is, unless you see why it's giving me that error.

Re: Min Date from three unrelated tables

mark_endicott — Thu, 05 Sep 2024 14:48:45 GMT

Tmk123 - I cant see your error without seeing the code you have written.

Besides I have tested the code on a replication of your data and it works, see below:

I have also attached my pbix file so you can see that I have structured it exactly as you have set out from your description. The only thing I have changed is the item numbers.

Re: Min Date from three unrelated tables

Tmk123 — Tue, 10 Sep 2024 12:55:07 GMT

Thank you!!