cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
muchtar Frequent Visitor
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

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

Re: group dates in table by filter/slicer option

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.
3 REPLIES 3
Highlighted
Community Support Team
Community Support Team

Re: group dates in table by filter/slicer option

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.
muchtar Frequent Visitor
Frequent Visitor

Re: group dates in table by filter/slicer option

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? 

Community Support Team
Community Support Team

Re: group dates in table by filter/slicer option

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.