cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EH_Matt
Regular Visitor

DAX Will Not Reference Columns

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!

6 REPLIES 6
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
WinterMist
Responsive Resident
Responsive Resident

@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).

WinterMist_2-1660326465076.png

 

Let me know if this is helpful to you.

 

Regards,

Nathan

WinterMist
Responsive Resident
Responsive Resident

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
Not applicable

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
Responsive Resident
Responsive Resident

@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.

 

WinterMist_0-1659647402254.png

 

Regards,

Nathan

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!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors