Forum Discussion

imous's avatar
imous
Frequent Visitor
9 years ago

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

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 ...
Need Help
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
9 years ago

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's avatar
imous
Frequent Visitor
9 years ago

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.