Replacing Measure with Calculated Column (or display ImageURL with Measure)

[imous]

I ran into the following problem. I have a TableA that contains a sensorId, timestamp and value column. I have a second tableB with a sensorId column, and a ManyToOne relationship between TableA and TableB. I wanted, among other things, to define in TableB a column with the maximum value from TableA, per sensorID, and with a timestamp within 2 hours of the last entry in TableA. I was only able to do this with Measures, first defining a column with

TwoHourTimestampMeasure = CALCULATE(MAXX(ALL(TableA); TableA[Timestamp]) - TIME(2;0;0))

and then a maximum column with

TwoHourMaxMeasure = CALCULATE(MAX(TableA[Value]);FILTER(TableA; TableA[Timestamp] > [TwoHourTimestampMeasure]))

Doing this with calculated columns didn't work, because I kept getting the maximum of ALL sensorIds. I tried using ALLEXCEPT(TableA; TableA[sensorID]) and ALLEXCEPT(TableB; TableB[sensorID]) in adition to the FILTER, but that didn't work either.

So I went ahead and used the measure. The problem is that I define an warning level depending on this maximum like so:

WarningLevel = CALCULATE(FLOOR([TwoHourMaxMeasure]/[AverageMeasure]+0,5;1))

and then tried to introduce a traffic light image into the table like so:

SensorStatus = IF([WarningLevel] > 3; "https://[Red].png"; IF([WarningLevel] = 3; "https://[Yellow].png"; "https://[Green].png"))

Unfortunately, I can't set the data category of a Measure to ImageURL, so I couldn't display the images in a table like I wanted. It would just display the URLs as text. I already made a trial version where this did work, but that one used Columns, not Measures.

My question (at last) is if anyone knows either how I can still display pictures in a table if a Measure determines which picture should be shown, or how I can duplicate my TwoHourMaxMeasure with a calculated column. The former is preferable, but I suspect the latter is more likely to be possible.

[v-sihou-msft]

@imous

Try measure like below:

=
VAR maxTime =
    CALCULATE ( MAX ( TableA[Timestamp] ), ALL ( TableA ) )
VAR maxWithinTwoHours =
    CALCULATE (
        MAX ( TableA[Timestamp] ),
        ALLEXCEPT ( TableA, TableA[sensorId] ),
        FILTER ( ALL ( TableA ), TableA[Timestamp] > ( maxTime - TIME ( 2, 0, 0 ) ) )
    )
RETURN
    CALCULATE (
        SUM ( TableA[Value] ),
        FILTER ( TableA, TableA[Timestamp] = maxWithinTwoHours )
    )

You have to get the max TableA time and max Timestamp within 2 hours group on sensorId level. There's no easier way.

If you still can't get expected result, please share some sample data.

Regards,

[imous]

Hi Simon. Thanks for the reply, but this doesn't look like what I need. I didn't have a problem getting the right value into my measure, the measures in my original post worked fine. The problem was that I needed columns, not measures, in order to properly show images in my final table by using ImageURLs. And I originally couldn't find a way to get those correct values into a column.

As mentioned in my second comment, I did fix that particular problem. I now have the right values in calculated columns, and I can use the ImageURLs like I wanted to. 

Since it's considered good practice to use measures wherever possible, I would prefer a solution where I can use only measures. But I assume there's no way to make a calculated column with ImageURLs based on a measure (as measure values are determined after calculated column values), and I found no way to explicitly define a measure as an ImageURL so that I can show images in a table. If I'm wrong in either assumption, I'd be happy to hear it. Otherwise, I'll stick with my currently working column solution.

[imous]

Okay, with some more trial and error I found that using

TwoHourMax = CALCULATE(MAX(TableA[Value]) ;FILTER(TableA; TableA[sensorID] = [sensorID] && TableA[Timestamp] > TableB[Max timestamp] - Time(2;0;0)))

does work. So it's less urgent, but I'm still open to any less ugly solutions.