Populating Column data in order table form different columns combination mix in Freight table

[Vemul]

Good Morning all,

Hope someone can help me here, I am new to power BI and working on to calculate Transport Lead Time for Dashboard.

I have two table to work with

• Freight Table

City	State	Shippoint	TDAYS (working days)
WINNELLIE	NT	General Warehouse	12
TOWNSVILLE	QLD	General Warehouse	10
POORAKA	SA	General Warehouse	7
Keysborough	VIC	General Warehouse	8
WINNELLIE	NT	Cold Chain	2
Adelaide	SA	Cold Chain	8
TOWNSVILLE	QLD	Cold Chain	4
POORAKA	SA	Cold Chain	4
WINNELLIE	NT	Cage/ Vault	3
TOWNSVILLE	QLD	Cage/ Vault	2
POORAKA	SA	Cage/ Vault	5

• Order Table

Delivery	CITY	State	Shipping point	TDAYS ( Expected Result)
80256260	WINNELLIE	NT	General Warehouse	12
80256391	TOWNSVILLE	QLD	General Warehouse	10
80256392	POORAKA	SA	General Warehouse	7
80256403	Adelaide	SA	Cold Chain	8
80256405	WINNELLIE	NT	Cold Chain	2
80256423	TOWNSVILLE	QLD	Cold Chain	4
80256424	POORAKA	SA	Cage/ Vault	4
80256425	WINNELLIE	NT	Cage/ Vault	3
80256426	TOWNSVILLE	QLD	Cage/ Vault	2
802563256	POORAKA	WA	Cold Chain	30 ( No Match )

Both tables have common fields to link but they can be in different combination to calculate result.

Example:

SA state has two Cities ( Pooraka & Adelaide ) and orders can be shipped from 3 shipping points ( General Warehouse, Cold Chain & Cage/ Vault ), so there will be possible 6 combinations. 

In that case Expected TDAYS in Order table should show like below

Delivery	CITY	State	Shipping point	TDAYS ( Expected Result)
80256392	POORAKA	SA	General Warehouse	7
80256403	Adelaide	SA	Cold Chain	8
80256424	POORAKA	SA	Cage/ Vault	4

If None of these combinations matching then TDAYS should show “30” days

Any help is great

Thanks

Vemul

[Vemul]

Hi Mate, 

no luck , showing belwo error

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

Expected result CC =
VAR _result =
    SUMMARIZE (
        FILTER (
            Freight,
            Freight[City] = 'Order'[CITY]
                && Freight[State] = 'Order'[State]
                && Freight[Shippoint] = 'Order'[Shipping point]
        ),
        Freight[TDAYS (working days)]
    )
RETURN
    IF ( ISBLANK ( _result ), 30, _result )

[Vemul]

Thanks for your quick Response.

but I am getting belwo error , is there somehting I am mising?

Thanks 

[Jihwan_Kim]

Hi,

I am not sure, but I think you are using different structure than the sample that you provided.

I think the sample showed only one expected result, however, yours shows more than one result as if you want to show more than one result.

The DAX formular that I wrote is meant to show table format result, but if it only contains one row &  one column, it also shows non-table format as well.

I am not sure what is your expected result if it shows more than one result. If you want to show MAX, then please try using MAXX insteas of using SUMMARIZE. If you want to show MIN, then please try using MINX instead of using SUMMARIZE. Or, what is your expected result when it contains more than one result?

[Vemul]

Thanks Agin, 

getting close.

I have achieved belwo by changing code but not sure how to populate "30" days if doesn't match any combinations 

any suggestions Please 

Thanks

Vijay

[Jihwan_Kim]

Hi,

Please try to write, 

LT Days calcualted column =
VAR  _yourresult = CALCULATE(..............................)

RETURN

IF (ISBLANK(_yourresult), 30, _yourresult)