Creating a measure from value on multiple related Tables (NEWB)

[I_Like_Pi]

I have 1 flat transaction data source that I have created 2 tables from. 1 is pretty much a straight dump of the data, and the other is built using the summarize command to get the Stats of the Stores, Average, and Std_DV.

The tables are related and I can confirm that is working because I can build presentation tables with values mixed from both. i.e. I have a sub set of the transactions with their values as a column and then the next 2 columns are the Avg and SD of the store for which the transaction belongs. I would expect the following to work.

Outlier = IF((ABS(Trans[Amount]-Store[Avg])>Store[SD],"Outlier","Not")

Where and how should I be creating this... Measure?Column? 

I am a newb to DAX and power BI but I can do this in my sleep in excel or access.

[I_Like_Pi]

Ah, I had created the 2nd table using the New Table button in the modelling tab, not in a query.

i.e.

Stores = Summarize(Trans,Trans[Stores],Trans[StoreState],"StoreTot",Sum(Trans[Amount]),"StoreAvg",AVERAGE(Trans[Amount]),"StoreSD",STDEV.P(Trans[Amount]),"StoreCnt",COUNTA(Trans[T_Ref])

Should I copy the original import steps for the Trans Import and then add the Summarize function? Is that the perfered method? Then I would be able to access the Merge Queries in the edit queries.

Regardless I am giving that a shot and I will see where I get.  

Thanks for the response and a path forward.

[I_Like_Pi]

Timing

... OK ... going with the Related() function

as building a whole new import query would be time consuming.

BRB

And thanks to both of you.

[CahabaData]

I would suggest you make a calculated column based on my interpretation of your post.

In the Data screen, the Edit Queries icon that opens the Query Editor - there one would Merge Queries to join your 2 tables, and then one would Add Column where you enter your DAX statement in the column header field it provides.

[jahida · Accepted solution]

I definitely don't think you need to do anything in Edit Queries (no need for a merge), but I do agree that making this as a calculated column in the Transaction table is your best bet. Note you'll need to add a RELATED() around any reference to a column in the other table.

[I_Like_Pi]

KABOOM

You are a rockstar jahida!!

Thanks,

I'm such a NEWB

I could not figure out why it would not accept a column from another table in the calc.

[jahida]

Haha thanks, yeah the related function is a bit to get your head around (doesn't feel like it should be necessary but is).