topic DAX Will Not Reference Columns in DAX Commands and Tips

DAX Will Not Reference Columns

EH_Matt — Thu, 04 Aug 2022 20:35:08 GMT

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!

Re: DAX Will Not Reference Columns

WinterMist — Thu, 04 Aug 2022 21:10:38 GMT

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

Re: DAX Will Not Reference Columns

Anonymous — Wed, 10 Aug 2022 08:17:52 GMT

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

Re: DAX Will Not Reference Columns

WinterMist — Fri, 12 Aug 2022 14:05:25 GMT

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

Re: DAX Will Not Reference Columns

EH_Matt — Fri, 12 Aug 2022 15:37:40 GMT

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!

Re: DAX Will Not Reference Columns

WinterMist — Fri, 12 Aug 2022 17:49:32 GMT

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

Re: DAX Will Not Reference Columns

Anonymous — Fri, 19 Aug 2022 07:15:13 GMT

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