Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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.
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.
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.
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.
Haha thanks, yeah the related function is a bit to get your head around (doesn't feel like it should be necessary but is).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |