Creating new dax column with particular conditions from online excel table

[jeongkim]

Hi,

I created control table in online excel to update while sharing visibility of new changes among stakeholders. 

I have data set and hope to create new WBS L1 column in dataset by looking up WBS L1 value from control table.

Frequency is main factor for WBS L1 mapping, meaning if Unit Price and Internal PO# (Effective from) are blank, then WBS L1 will follow for all relevant Frequency. 

If either Unit price or Internal PO# (Effective from) are not blank, WBS L1 will follow for only that conditions.

Please help to create new column with DAX. 

I can easily make dax script using IF conditions specifying this conditions but there will be more changes in Control table conditions in the future, so I would like to get these conditions from the online excel control table rather than typing it in DAX one by one. 

Control table 

Result

[v-hashadapu]

Hi @jeongkim , 

Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[v-hashadapu]

Hi @jeongkim , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.

[v-hashadapu]

Hi @jeongkim , Hope you are doing well. Kindly let us know if the issue has been resolved or if further assistance is needed. Your input could be helpful to others in the community.

[Mvsainathareddy]

Hi @jeongkim 

1. Normalize Your Control Table
Make sure your control table is structured such that each row represents a unique rule, with columns for each conditional factor (e.g., Frequency, Unit Price, Internal PO#, WBS L1) and—if needed—comments for logic explanations. This makes the table easily expandable and readable by DAX.
2. Establish Relationships (if possible)
If your control and data tables share matching columns (e.g., Frequency), create relationships between these tables. However, for flexible or multi-field lookups, you can proceed without relationships.
3. Dynamic Multi-Condition Lookup with DAX
You should use the LOOKUPVALUE function (or a combination with CALCULATE + FILTER) to reference the control table based on multiple conditions.
Example DAX Column
Let’s assume:
•Your data table is named Dataset
•Your control table is named Control
•You want to match on Frequency. When Unit Price and Internal PO# are blank, choose the WBS L1 for that frequency. Otherwise, use more specific rows from the control table as appropriate.
WBS L1 =
VAR _unitPrice = Dataset[Unit price]
VAR _po = Dataset[Internal PO#]
VAR _frequency = Dataset[Frequency]
RETURN
LOOKUPVALUE(
Control[WBS L1],
Control[Frequency], _frequency,
Control[Unit price], IF(ISBLANK(_unitPrice), BLANK(), _unitPrice),
Control[Internal PO#], IF(ISBLANK(_po), BLANK(), _po)
)

•This formula will lookup the WBS L1 based on exact matches. When Unit Price and PO are blank, it matches only on Frequency.
Further Logic for Fallbacks
If you want a fallback to just frequency when detailed matches fail (i.e., first try the specific match, else fallback to general frequency), you can use:
WBS L1 =
VAR _result =
LOOKUPVALUE(
Control[WBS L1],
Control[Frequency], Dataset[Frequency],
Control[Unit price], Dataset[Unit price],
Control[Internal PO#], Dataset[Internal PO#]
)
RETURN IF(
ISBLANK(_result),
LOOKUPVALUE(
Control[WBS L1],
Control[Frequency], Dataset[Frequency],
Control[Unit price], BLANK(),
Control[Internal PO#], BLANK()
),
_result
)

If this helped, please consider giving kudos and mark as a solution

[jeongkim]

Thanks but how does it control PO# whether it is bigger than 16. 

It needs to control only from specific PO# as the table. 

[v-hashadapu]

Hi @jeongkim , Thank you for reaching out to the Microsoft Community Forum.

The issue is because your WBS L1 mapping rules depended on multiple conditions and these couldn’t be handled with a simple relationship or single-column lookup, especially since blanks and mixed data types in your control table caused matching failures. We built sample data based on your scenario and screenshots, cleaned both Dataset and Control in Power Query to standardize blanks, trim spaces and ensure numeric types, then created a calculated column in Dataset with DAX that dynamically looks up the correct WBS L1 from your online Excel control table following your exact rules.

You can refer to the attached .pbix file for a working example and share your thoughts.

Thank you.