Forum Discussion

Kristianhk's avatar
Kristianhk
Frequent Visitor
6 years ago
Solved

graph with dynamic start and end form selected row

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

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Kristianhk
      Frequent Visitor
      6 years ago

      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

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Kristianhk
      Frequent Visitor
      6 years ago

      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's avatar
        v-alq-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi, Kristianhk 

         

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

         

        Best Regards

        Allan

    • Kristianhk's avatar
      Kristianhk
      Frequent Visitor
      6 years ago

      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