+ Reply to Thread
Results 1 to 3 of 3

DAX Measure result differs due to relationship between tables

  1. #1
    Registered User
    Join Date
    01-24-2020
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    95

    DAX Measure result differs due to relationship between tables

    Dear gurus,

    I came across an problem which drives me up the wall! So I turn for help! Any help is much appreciated!

    I wrote a dax measure for running total in a very simple data model. All seems ok as no relationship is established between the dDates table and the fTxn; by accidental, I establish a relationship between these two table, it turned out that the seemingly-right dax measure went WRONG!

    I went through quite some articles and posts concerning relationship or filter propagation in Power BI; but still couldn't wrap my head over the issue. Could you pls do me the favor? Tks in advance!

    (Attached is the .pbix file)
    Attached Files Attached Files

  2. #2
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    4,234

    Re: DAX Measure result differs due to relationship between tables

    I'm very new to DAX.

    IMHO,

    CALCULATE (
    SUM ( fTxn[Qty] ),
    FILTER ( ALL ( fTxn[Date]), fTxn[Date] <= MAX ( dDates[Date] ) ))

    With [Date] Relationship, fTxn[Date] will has 1 day by filter context from dDates[Date] slicer by Date Relationship direction from dDates => fTxn .



    Please use this
    CALCULATE (
    SUM ( fTxn[Qty] ),
    FILTER ( ALL ( dDates[Date] ), dDates[Date] <= MAX ( dDates[Date] ) ))

    Same field with the slicer , All override filter context.

  3. #3
    Registered User
    Join Date
    01-24-2020
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    95

    Re: DAX Measure result differs due to relationship between tables

    Quote Originally Posted by Bo_Ry View Post
    I'm very new to DAX.

    IMHO,

    CALCULATE (
    SUM ( fTxn[Qty] ),
    FILTER ( ALL ( fTxn[Date]), fTxn[Date] <= MAX ( dDates[Date] ) ))

    With [Date] Relationship, fTxn[Date] will has 1 day by filter context from dDates[Date] slicer by Date Relationship direction from dDates => fTxn .



    Please use this
    CALCULATE (
    SUM ( fTxn[Qty] ),
    FILTER ( ALL ( dDates[Date] ), dDates[Date] <= MAX ( dDates[Date] ) ))

    Same field with the slicer , All override filter context.
    Tku so much, Bo_Ry, for walking me through!

    If I'm understanding it correctly, the slicer (dDates[Date]) isn't overridden cuz column name of fTxn[Date] in CALCULATE is different, dDates[Date] filtering keeps active into the scope of CALCULATE's evaluation context.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Can not create relationship between Tables (not many to many)
    By vipe110 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-07-2018, 08:36 AM
  2. linking tables with a relationship instead of vlookup
    By Atomic BI in forum Excel General
    Replies: 0
    Last Post: 03-29-2016, 12:48 PM
  3. [SOLVED] What am doing wrong? Creating a relationship between two tables.
    By BrownBoy in forum Access Tables & Databases
    Replies: 2
    Last Post: 07-18-2014, 11:52 AM
  4. TEXT function result differs between users
    By mandersten in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-02-2013, 11:00 AM
  5. measure staff turnover by using pivot tables
    By mrsbear in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-09-2012, 05:47 PM
  6. Relationship between Cell and Data Tables
    By NeshaNYC in forum Excel General
    Replies: 0
    Last Post: 08-15-2011, 10:55 AM
  7. Weekday() result differs from one computer to another ???
    By bobm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-11-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts