cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imous
Frequent Visitor

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 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.

 

3 REPLIES 3
v-sihou-msft
Microsoft
Microsoft

@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,

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
Frequent Visitor

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors