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]))...
Need Help
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

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

      Thanks. I will give that a try.

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

        Hi rrhutch,

         

        Could you please tell me if your problem was resolved? Please mark the proper answer if it worked. That will be a help to the others.

        Best Regards!
        Dale