Reply
Frequent Visitor
Posts: 2
Registered: ‎12-05-2018
Accepted Solution

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? 


Accepted Solutions
Highlighted
Community Support Team
Posts: 5,399
Registered: ‎09-21-2016

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

View solution in original post


All Replies
Highlighted
Community Support Team
Posts: 5,399
Registered: ‎09-21-2016

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

Frequent Visitor
Posts: 2
Registered: ‎12-05-2018

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
Posts: 5,399
Registered: ‎09-21-2016

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