Issue figuring out a filter in a measure

[rrhutch]

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?

[v-jiascu-msft]

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]

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]

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]

Thanks. I will give that a try.

[v-jiascu-msft]

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

[rrhutch]

I have had to shift to other projects for now, so when I get back around to this one, I will update it.