Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
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 |
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
Hi Mate,
no luck , showing belwo error
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 )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks for your quick Response.
but I am getting belwo error , is there somehting I am mising?
Thanks
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?
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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
Hi,
Please try to write,
LT Days calcualted column =
VAR _yourresult = CALCULATE(..............................)
RETURN
IF (ISBLANK(_yourresult), 30, _yourresult)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
128 | |
72 | |
70 | |
58 | |
53 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |