Forum Discussion

Giuseppe_a97's avatar
Giuseppe_a97
New Member
3 years ago

Power bi desktop function (representation)

Hi, I've a Bi desktop problem that I can't fix. I've a table with the following columns: number of train, train maintenance facility, time of departure from station "x", time of arrival at station "y...
Need Help
  • Giuseppe_a97's avatar
    Giuseppe_a97
    New Member
    3 years ago

    Greg_Deckler thanks for the answer, but I can't fix yet. I try to post a simple example:

     

    maintenance facility --- Departure hour --- Arrival hour --- train

    Milano                            07.00                      12.00                  CA7183

    Roma                              09.00                      13.00                  AX9283

    Napoli                            14.00                       20.00                  BY3273

    Milano                            12.00                       21.00                  CI8213

    Milano                            12.30                       14.00                  AM1324

    Roma                              18.00                       19.00                  OH1132

     

    Now the question is: how many trains of the Milan maintenance facility are in operation from 9 to 15?

    I would like to have a table/graph that show me this information

     

    Thanks

      

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      3 years ago

      Giuseppe_a97 Use a disconnected time table with the hours you want to track and this measure. PBIX is attached below signature.

      Measure = 
    VAR __Table = 
        SELECTCOLUMNS(
            FILTER(
                GENERATE(
                    'Table',
                    'Times'
                ),
                [Hour] >= [departure hour] && [Hour] <= [arrival hour]
            ),
            "Train",[train],
            "Hour",[Hour]
        )
    VAR __Table1 = GROUPBY(__Table, [Train], "Count", COUNTX(CURRENTGROUP(),[Hour]))
    VAR __Result = COUNTROWS(__Table1)
RETURN
    __Result