DAX Will Not Reference Columns

[EH_Matt]

Hi All,

I have two tables:

- Table A: contains project information such as ID, start date, end date, etc. These are all columns.

- Table B: contains financial information such as Total Actuals, Budget, etc. These are all measures based on another table.

I need to create a measure in Table A that references the Start Date column, as well as two of the financial measures from Table B. However, when trying to create the measure, the "IF" statement will not reference the Start Date column from Table A. Do you know why this is? I am trying to do something like...

IF(And(Year(Project Start Date) = 2021, Total Forecast - Total Actuals > 0), Total Forecast - Total Actuals, 0)

Any thoughts or advice would be much appreciated!

[v-shex-msft]

Hi @EH_Matt,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[WinterMist]

@EH_Matt 

Sorry to hear you are locked out from creating calculated columns.

You can create the measure in the following way.

However, you will need to combine the columns from both Table A & Table B into the same table visual.

This is because measures calculate at the report level and not at the row level.

NOTE: I left the calculated column (C1 Calc) in the table visual for comparison with the measure (M1 Calc).

Let me know if this is helpful to you.

Regards,

Nathan

[WinterMist]

Hello @EH_Matt,

Were you able to get this working with the calculated column?

If so, please consider marking this as a solution.

If not, please let us know.

NOTE: @Anonymous is correct that calculated columns cannot dynamically recalculate based on filter (or slicer) selection.  However, based on your request, I did not see anything regarding filter or slicer selection.  For this reason, I still believe a calculated column is the best solution.  (Calculated columns will only recalculate when the data is refreshed.)

Regards,

Nathan

[Anonymous]

Hi @EH_Matt,

AFAIK, current power bi does not support create dynamic calculated columns based on filter effects. 

Notice: data level from parent to child, the child not able effect parent level.

query table(query parameter, custom function...) -> data model table(calculate table, calculate column, RLS) -> date view table (measure, slicer, filter)

Regards,

Moonlight

[WinterMist]

@EH_Matt 

DAX cannot find the column because there is no RC (Row Context) provided.

DAX doesn't know which row to specify.

I believe it would be better to use a Calculated Column (RC auto-included) rather than a measure.

The following should work for you.

Regards,

Nathan

[EH_Matt]

Thanks for the suggestion. I'm not sure - I think it could've worked, but the way the initial dataset was designed, they have me locked out from creating columns. Thanks for the assist though!