Forum Discussion

I_Like_Pi's avatar
I_Like_Pi
Icon for Resolver II rankResolver II
10 years ago
Solved

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

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.

Need Help
  • jahida's avatar
    jahida
    10 years ago

    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.

6 Replies

  • CahabaData's avatar
    CahabaData
    Icon for Memorable Member rankMemorable Member
    10 years ago

    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's avatar
      jahida
      Icon for Impactful Individual rankImpactful Individual
      10 years ago

      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's avatar
        I_Like_Pi
        Icon for Resolver II rankResolver II
        10 years ago

        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.

  • I_Like_Pi's avatar
    I_Like_Pi
    Icon for Resolver II rankResolver II
    10 years ago

    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's avatar
      I_Like_Pi
      Icon for Resolver II rankResolver II
      10 years ago

      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.