graph with dynamic start and end form selected row

[Kristianhk]

Hey Guys

I have tried to make a solution and/or searching for on without any luck.

I have a dataset starttime and endtime  (datetime (datetime)) for each product ID from a machine. 

I have another dataset with temperatures and a timestamp (datetime) for the logging time.

I want to have a table with all the product ID and start/end time. when I select one a graph should show be the temperature within starttime and endtime

do I need to have a dynamic table with the datetime in bewteeen, or how can I do this

Thanks

Kristian

[v-alq-msft · Accepted solution]

Hi, @Kristianhk 

Based on your description, I created data to reproduce your scenario.

Table:

DateTable:

DateTable = CALENDARAUTO()

Then you may create a measure as follows.

IsDisplay = 
var _currentproduct = SELECTEDVALUE('Table'[Product ID])
var _currentdate = MIN(DateTable[Date])

return
IF(
        _currentproduct<>BLANK(),
        IF(
            _currentdate <= VALUES('Table'[endtime])&&
            _currentdate >= VALUES('Table'[starttime]),
            1,
            0
        )
)

Finally, you may put the measure in the visual level filter. Here is the result.

If I misunderstand your thought, please show me your sample data and expected output. Do mask sensitive data before uploading. I am glad to solve the problem for you.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kristianhk]

Hi Allan (@v-alq-msft)

I got it working!

I changed 

_currentdate = MIN(DateTable[Date])

to

_currentdate = MIN(Temperatures[Date])

and then it worked like a charm!!..  I did not need a DateTable after all

thank you for your help

[Kristianhk]

Hi Allan

I ams orry for the very late reply.  I think you are on the right track to solve the issue. however the filtered table with temperatures is blank when I put on the IsDisplay filter. I think the issue is that my start time and end time are having the format datetime and the IsDisplay are looking at dates and 12:00 AM.  

how to fix?

best regards

Kristian

[v-alq-msft]

Hi, @Kristianhk 

You may change the data type to datatime in the 'Modeling' ribbon .

Best Regards

Allan

[amitchandak]

One of the ways is to bring the temperature into the table having the product. This can be done with a formula similar to one given below. But you will be able to bring min or max temperature during that time. Not all

A new Column in table 1 = minx(filter(table2,table1[col1] =table2[col1] && table1[col2] =table2[col2]),table1[col4])

A new Column in table 1 = maxx(filter(table2,table1[col1] =table2[col1] && table1[col2] =table2[col2]),table1[col4])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Kristianhk]

Hi @amitchandak 

thank you for your answer,

I have not tried your solution yet what I want is every temperature readings within the start end time for the selected product.

table A:

Start datetime | End datetime | machine ID | Product ID | ............

table B:
datetime | machine ID | Product ID | Temperature

so when I select a row in table A I want a graph showing me all temperatures (table B) between start time and end time for that machine

[amitchandak]

Please check how we created active employees in this blog. The same way you should able to get all the temperature.

HR-Analytics-Active-Employee-Hire-and-Termination-trend