Forum Discussion

rrhutch's avatar
rrhutch
Icon for Helper II rankHelper II
9 years ago

Issue figuring out a filter in a measure

I had a New Column working with the following CALCULATE and FILTER functions:

 

MAXRECORD = CALCULATE(MAX([CREATE DATE TIME]),FILTER(TableA,[CONCATENATECOMPARE]=EARLIER(TableA[CONCATENATECOMPARE])))

 

CONCATENATECOMPARE is a field that I have in the table to create context for the Calculation.

 

However, I tried to add another filter based on a field I created from the Slicer value someone chooses. The slicer is running of a table of dates that I am basically using as a parameter table. The issue I ran into is that I can only use the field created from the slicer value in a Measure, and I can only use the EARLIER function in a Column.

 

The calcuation I was trying was:

 

MAXRECORD = CALCULATE(MAX([CREATE DATE TIME]),FILTER(TableA,[CONCATENATECOMPARE]=EARLIER(TableA[CONCATENATECOMPARE])),FILTER(TableA, [CREATE_DATE]<= TableB[Date1 Slicer]))

 

Thoughts on how I can accomplish this?

Need Help

6 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi rrhutch,

     

    Maybe you could check these things below.

    1. Does the table A have a relationship with the table B? 

    2. Did you try functions "RELATED" and "RELATEDTABLE"? If the relationship is 1:1, then we could use "RELATED".

    MAXRECORD =
CALCULATE (
    MAX ( [CREATE DATE TIME] ),
    FILTER ( TableA, [CONCATENATECOMPARE] = EARLIER ( TableA[CONCATENATECOMPARE] ) ),
    FILTER ( TableA, [CREATE_DATE] <= RELATED ( TableB[Date1 Slicer] ) )
)

    If the relationship is 1:*, then we could use "RELATEDTABLE".

    MAXRECORD =
VAR temp =
    CALCULATE (
        SELECTCOLUMNS (
            RELATEDTABLE ( TableB ),
            "min/max", MIN ( TableB[Datea Slicer] )
        )
    )
RETURN
    CALCULATE (
        MAX ( [CREATE DATE TIME] ),
        FILTER ( TableA, [CONCATENATECOMPARE] = EARLIER ( TableA[CONCATENATECOMPARE] ) ),
        FILTER ( TableA, [CREATE_DATE] <= temp )
    )

    Best Regards!

    Dale

    • rrhutch's avatar
      rrhutch
      Icon for Helper II rankHelper II
      9 years ago

      Thanks. The tables do not have a relationship. I am unable to create one b/c the resulting value from the slicer is not the same date type.

       

      I did try the RELATEDTABLE option. Unfortunately, in setting the variable it says it is unable to find or cannot use TableB[Date1 Slicer] in the expression.

       

      I think the issue is that the data type for the measure that is used to create the [Date1 Slicer] field is text. You are unable to change it. I have tried using a DATEVALUE function to convert it, but it says that it is unable to convert the variant type. 

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi rrhutch,

         

        1. [Date1 Slicer] should be in a proper date format though the format of the field is text.

        2. Maybe we can try this. 

        MAXRECORD =
IF (
    HASONEVALUE ( tableb[date1 slicer] ),    CALCULATE (
        MAX ( [CREATE DATE TIME] ),
        FILTER ( TableA, [CONCATENATECOMPARE] = EARLIER ( TableA[CONCATENATECOMPARE] ) ),
        FILTER ( TableA, [CREATE_DATE] <= min(datevalue(TableB[Date1 Slicer] )))    ),
    Something else
        )

        Best Regards!

        Dale