cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

error message not sure what to do

error.GIF

 

i have this message when  trying to get a value from a column

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi, @FCF 

 

You may try the following calculated column or measure.

Calculated column:

Column = 
Targets[ Target Margin(CDN$) ]-
CALCULATE(
    SUM(Sales[act_mar]),
    FILTER(
        ALL(Sales),
        [Week]=EARLIER(Targets[Wk #])
    )
)

 

Measure:

Measure = 
SELECTEDVALUE(Targets[ Target Margin(CDN$) ])-
CALCULATE(
    SUM(Sales[act_mar]),
    FILTER(
        ALL(Sales),
        [Week]=SELECTEDVALUE(Targets[Wk #])
    )
)

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
Super User II
Super User II

@FCF

If you are creating a measure, you cannot refer to a column this way. The measure returns a single value, so you must use one of the aggregate functions (SUM, MIX, MAX etc.).

If you want to compute something at row level then add a calculated column - then you can do it just like you do now.



_______________
If I helped, please accept the solution and give kudos! 😀

hi

thanks for the response, i am really new to this and am not sure what you mean by creating a calculated column

@FCF 

If you have a table and you want to add an additional column that calculates something based on other columns in this table, then you click calculated New Column and you create a formula to calculate.

lkalawski_0-1598621976668.png

 

You can read more about it here: https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

 



_______________
If I helped, please accept the solution and give kudos! 😀

Hi again

the values that i want to work with come from 2 differnet tables.

i have created the calculated column, but am not able to see one of the columns form a differnt table.

this should not be so confusing.

thanks

@FCF , screenshot is not making clear, column, or measure. Both ways it is looking wrong. No need for the table name first. If measure the second one needs to aggeration.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

this is an example of the targets.xlxs table

SalespersonCustomerWk #Reference Target Sales (CDN$)  Target Margin(CDN$) YR
JIMVAPLWE2JIMVAPLWE2615145.382020
JIMVAPLWE4JIMVAPLWE4615139.792020
JIMVAPLWE6JIMVAPLWE6630266.22020
JIMVAPLWE7JIMVAPLWE717135.512020
JIMVAPLWE8JIMVAPLWE8652181.592020
JIMVAPLWE10JIMVAPLWE10615145.382020
JIMVAPLWE12JIMVAPLWE12615156.582020
JIMVAPLWE14JIMVAPLWE14615117.42020
JIMVAPLWE16JIMVAPLWE16696141.732020
JIMVAPLWE18JIMVAPLWE18619137.252020
JIMVAPLWE20JIMVAPLWE20619148.452020
JIMVAPLWE22JIMVAPLWE22761181.842020
JIMVAPLWE24JIMVAPLWE24622152.022020
JIMVAPLWE26JIMVAPLWE26622124.042020
JIMVAPLWE28JIMVAPLWE28624153.742020
JIMVAPLWE30JIMVAPLWE30711193.032020
JIMVAPLWE32JIMVAPLWE32608137.872020
JIMVAPLWE34JIMVAPLWE34644173.632020
JIMVAPLWE36JIMVAPLWE36743158.372020
JIMVAPLWE38JIMVAPLWE38603127.632020
JIMVAPLWE41JIMVAPLWE41781177.062020
JIMVAPLWE42JIMVAPLWE42611141.112020
JIMVAPLWE44JIMVAPLWE44697154.792020
JIMVAPLWE46JIMVAPLWE46618137.182020
JIMVAPLWE52JIMVAPLWE52604139.522020

sales table

CustomerPosting dateSales (CDN$)ActualMarginSalespersonWeekYearSell-to customer noKey3act_mar
INPRPI28/12/201631.54.5JIM522016INPRPIINPRPIJIM2016524.5
INPRPI28/12/201631.54.5JIM522016INPRPIINPRPIJIM2016524.5
INPRPI28/12/201631.54.5JIM522016INPRPIINPRPIJIM2016524.5
INPRPI21/12/201631.54.5JIM512016INPRPIINPRPIJIM2016514.5
INPRPI20/12/201631.54.5JIM512016INPRPIINPRPIJIM2016514.5
INPRPI07/12/201631.54.5JIM492016INPRPIINPRPIJIM2016494.5
INPRPI05/12/201631.54.5JIM492016INPRPIINPRPIJIM2016494.5
INPRPI05/12/201631.54.5JIM492016INPRPIINPRPIJIM2016494.5
INPRPI05/12/201631.54.5JIM492016INPRPIINPRPIJIM2016494.5
INPRPI30/11/201631.54.5JIM482016INPRPIINPRPIJIM2016484.5
INPRPI30/11/201631.54.5JIM482016INPRPIINPRPIJIM2016484.5
INPRPI30/11/201631.54.5JIM482016INPRPIINPRPIJIM2016484.5
INPRPI05/10/201631.54.5JIM402016INPRPIINPRPIJIM2016404.5

3rd table

calendar

DateM-#YrFiscalQuarterM-NameWkMonthWK#orderord1ord2wk_sort
28/07/1972019FY20Q1-20Jul31.197~Jul31.191.001.201.20031.191
29/07/1972019FY20Q1-20Jul31.197~Jul31.191.001.201.20131.192
30/07/1972019FY20Q1-20Jul31.197~Jul31.191.001.201.20231.193
31/07/1972019FY20Q1-20Jul31.197~Jul31.191.001.201.20331.194
01/08/1982019FY20Q1-20Aug31.198~Aug31.191.001.201.20431.195
02/08/1982019FY20Q1-20Aug31.198~Aug31.191.001.201.20531.196

i have used the calendar to create the relationships 

from the target tabel i want to pull the [Target Margin(CDN$) ] -sales table [act_mar] = [margin_Variance]

 

i hope this helps.

Community Support
Community Support

Hi, @FCF 

 

You may try the following calculated column or measure.

Calculated column:

Column = 
Targets[ Target Margin(CDN$) ]-
CALCULATE(
    SUM(Sales[act_mar]),
    FILTER(
        ALL(Sales),
        [Week]=EARLIER(Targets[Wk #])
    )
)

 

Measure:

Measure = 
SELECTEDVALUE(Targets[ Target Margin(CDN$) ])-
CALCULATE(
    SUM(Sales[act_mar]),
    FILTER(
        ALL(Sales),
        [Week]=SELECTEDVALUE(Targets[Wk #])
    )
)

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi

thanks for the help with this problem, i will test the two options.

Hi

I have found a way to make the measure work for me thanks for your help.

good morning

thanks for the response, 

i have tried the measure but the reference to [week] is not working.  i have an error message 

"column week cannot be found or may not be used in this expression."

is it possible to upload the pbx file here?

@FCF

Can you provide details of what you want to do, e.g. adding sample data?

To get data from another table, you must use the RELATED function.

https://dax.guide/related/



_______________
If I helped, please accept the solution and give kudos! 😀 

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors