Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Create Measure Filtering Range of Column Value by Date Column in Different Table

I have a data model with two tables. Table EST has estimated value for X by Date Table ACT has actual values for X by Date I would like to create a new Measure (NewerX) that filters values from ...
Need Help
v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

What is the relationship between two tables? we make  a sample and get the expected result:

 

 

Please try to set the ACT[Date] as not summarize, if it does not work, please try to use the following measure:

Measure = if(min(ACT[Date]) > Max(EST[Date]), SUM(ACT[X]), BLANK())

 


By the way, PBIX file as attached.


Best regards,

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thank you for the help. 

I am still not getting the expected values.

 

Here is screenshot

 

 

 

NewerACWP = if(min('Jamis'[APEndDate]) > max('CobraSummary'[Date]) && min('Jamis'[APEndDate]) > min('CobraSummary'[Date]), sum('Jamis'[BurdenedCost]), BLANK())

 

 
The left visual is Jamis (ACT), middle visual is CobraSummary (EST) and right visual is measure.

ACT (Jamis) and EST (CobraSummary) are related through field called "TO" (in drop-down slicer).

Does this help any in discerning what I am doing wrong?

Thank you!

 

 

 

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    It seems because 'Jamis' table cannot filter the 'CobraSummary' Table correctly, if you have a middle table such as following:

     

    'Jamis'[TO] M<--1 'TOTable'[TO] 1 -->M 'CobraSummary'[TO]

     

    Please try to use the following code:

     

    NewerACWP =
IF (
    MIN ( 'Jamis'[APEndDate] )
        > CALCULATE (
            MAX ( 'CobraSummary'[Date] ),
            FILTER (
                ALLSELECTED ( 'CobraSummary' ),
                'CobraSummary'[TO] IN DISTINCT ( 'TOTable'[TO] )
            )
        ),
    SUM ( 'Jamis'[BurdenedCost] ),
    BLANK ()
)

     

    If you have relationship as following

     

    'Jamis'[TO]  M<---->M 'CobraSummary'[TO]

     

    Please try to use the following formula:

     

    NewerACWP =
IF (
    MIN ( 'Jamis'[APEndDate] )
        > CALCULATE (
            MAX ( 'CobraSummary'[Date] ),
            FILTER (
                ALLSELECTED ( 'CobraSummary' ),
                'CobraSummary'[TO] IN DISTINCT ( 'Jamis'[TO] )
            )
        ),
    SUM ( 'Jamis'[BurdenedCost] ),
    BLANK ()
)

     

    Or you can use the following:

     

    NewerACWP =
IF (
    MIN ( 'Jamis'[APEndDate] )
        > CALCULATE ( MAX ( 'CobraSummary'[Date] ), ALLSELECTED () ),
    SUM ( 'Jamis'[BurdenedCost] ),
    BLANK ()
)

     


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,