Forum Discussion
need help this calculated table by using dax
Could you please help me create a calculated table in Power BI using DAX? I have two tables: Calendar and Inbound_Query. The Inbound_Query table includes the following columns: Market, Supply Catego...
Hi,
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
expected result table =
VAR _condition = SELECTCOLUMNS(
SUMMARIZE(
ADDCOLUMNS(
ADDCOLUMNS(
SUMMARIZE(
inbound_query,
inbound_query[market],
inbound_query[supply_category],
inbound_query[max_eta],
inbound_query[capacity]
),
"@container_count", CALCULATE(COUNTROWS(DISTINCT(inbound_query[container_number])))
),
"@condition", IF(
inbound_query[capacity] < [@container_count],
1,
0
),
"@remaining_containers_count", IF(
[@container_count] - inbound_query[capacity] > 0,
[@container_count] - inbound_query[capacity],
0
)
),
inbound_query[market],
inbound_query[supply_category],
inbound_query[max_eta],
[@container_count],
[@remaining_containers_count],
[@condition]
),
"@m", inbound_query[market],
"@s", inbound_query[supply_category],
"@container_count", [@container_count],
"@remaining_containers_count", [@remaining_containers_count],
"@maxeta", inbound_query[max_eta],
"@condition", [@condition]
)
VAR _t = GENERATE(
inbound_query,
FILTER(
_condition,
[@m] = inbound_query[market] && [@s] = inbound_query[supply_category] && [@maxeta] = inbound_query[max_eta]
)
)
RETURN
SUMMARIZE(
ADDCOLUMNS(
SUMMARIZE(
_t,
inbound_query[market],
inbound_query[supply_category],
inbound_query[max_eta],
[@container_count],
[@remaining_containers_count],
inbound_query[capacity],
inbound_query[clearance_days],
[@condition]
),
"@predicted_delivery_date", inbound_query[max_eta] + inbound_query[clearance_days] + [@condition]
),
inbound_query[market],
inbound_query[supply_category],
inbound_query[max_eta],
[@container_count],
[@remaining_containers_count],
inbound_query[capacity],
inbound_query[clearance_days],
[@predicted_delivery_date]
)The balance containers need to be carried forward to the next day, even though the input data does not have continuous dates
Hi tkavitha911,
As per my analysis, the solution provided by Jihwan_Kim matches the requirement.
However, to assist you further and provide an accurate and working solution, could you please help clarify a few things:
- Date Context: Is the expected output a daily view across a calendar, or are we only interested in dates that appear in the Max ETA date column from the Inbound Query table?
- When you say you want "carried forward to the next day," in which column do you want the day to be carried forward?
- Please provide a sample data/ pbix to get more clarity about your situation.
Your explanation will give us more clarity and hence we would be able to assist you better on this.
Regards,
Vinay Pabbu
