Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FCF
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
v-alq-msft
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
lkalawski
Memorable Member
Memorable Member

@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

lkalawski
Memorable Member
Memorable Member

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

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.

v-alq-msft
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.

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?

lkalawski
Memorable Member
Memorable Member

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.