Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Time table issue - (for stacked bar chart)

Thank you for your time, in advance!   I am using DirectQuery for SQL. Data cannot be imported because the ddbb is too huge. In that main/fact table I have a column "date"-dd/MM/yyyy and a column "...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi v-danhe-msft!

    First of all, thank you very much for your answer. 

     

    I've figured out that the relationship between the fact table and the time table was always wrong and it didn't filter properly, and I couldn't aply only your solution because I needed it to be dinamic for all hours. But it helped me to see time formats. 


    It was all wrong because my sql time column had nanoseconds. So I just taken out the nanosecond in my sql and voila!! the data conected properly. 

     

    The purpose was this graphic. Now I have the operations by language and hours of the day. The operations made between 17:00:00 and 17:59:59 are stacked up by language in one bar, and so on...

     

     

v-danhe-msft's avatar
v-danhe-msft
Microsoft Employee
7 years ago

Hi Anonymous,

From your description, I could not understand how do you want to filter your data table, if you want to filter all times between 8:00:00 and 8:59:59, you could use below formula in dax:

Create a new table:

 

Table = CALCULATETABLE('Query1',FILTER('Query1',TIME(8,0,0)<='Query1'[DayTime]&&Query1[DayTime]<TIME(9,0,0)))

Result:

 

You could also download the pbix file to have a view.

 

Regards,

Daniel He

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-danhe-msft!

    First of all, thank you very much for your answer. 

     

    I've figured out that the relationship between the fact table and the time table was always wrong and it didn't filter properly, and I couldn't aply only your solution because I needed it to be dinamic for all hours. But it helped me to see time formats. 


    It was all wrong because my sql time column had nanoseconds. So I just taken out the nanosecond in my sql and voila!! the data conected properly. 

     

    The purpose was this graphic. Now I have the operations by language and hours of the day. The operations made between 17:00:00 and 17:59:59 are stacked up by language in one bar, and so on...