Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
muchtar
Frequent Visitor

group dates in table by filter/slicer option

Hi,

 

in my table I've a column timestamp with many rows of timestamps for about one month.

I want to build a filter/slicer, so that the user can choose if he want to group the table rows by day, by week or by month.

 

In my example I've two tables and each of them have the column timestamp.  I created new group by options for each table. For the first group by 1 day and the second group by 7 days.

 

image.pngimage.png

 

image.pngimage.png 

 

What I want to do is to have one table which contain all rows and a filter/slicer or somthing else with "group by" option. So when the user choose a "group by" option, the table get formated. Is something like that possible in Power BI Desktop/Service? 

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @muchtar,

 

Based on my assumption, after adding two group bins, the source table is structured as below:

1.PNG

 

Based on above table, please new a calculated table.

Sample3_1 =
UNION (
    SELECTCOLUMNS (
        Sample3,
        "Type", "Day Span",
        "Date", Sample3[Day Span],
        "Value", Sample3[Value]
    ),
    SELECTCOLUMNS (
        Sample3,
        "Type", "Week Span",
        "Date", Sample3[Week Span],
        "Value", Sample3[Value]
    )
)

Add Sample3_1[Type] into slicer, add [Date] and [Value] into table visual, the preview result is like:

1.PNG2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @muchtar,

 

Based on my assumption, after adding two group bins, the source table is structured as below:

1.PNG

 

Based on above table, please new a calculated table.

Sample3_1 =
UNION (
    SELECTCOLUMNS (
        Sample3,
        "Type", "Day Span",
        "Date", Sample3[Day Span],
        "Value", Sample3[Value]
    ),
    SELECTCOLUMNS (
        Sample3,
        "Type", "Week Span",
        "Date", Sample3[Week Span],
        "Value", Sample3[Value]
    )
)

Add Sample3_1[Type] into slicer, add [Date] and [Value] into table visual, the preview result is like:

1.PNG2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

hi sir, if i have a column such as city or product type to filter the value then how to do ?

 

Hi,

 

thank you for your help, but when I do it like that, I'm getting following error;

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

 

Do you know how to solve this?

 

 

Based on above table, please new a calculated table.

And how do I calculate a new table? 

Hi @muchtar,

 

Click the "New Table" button under Modeling tab, paste the above provided DAX formula. Remember to replace the table name, column name with your own actual data.

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.