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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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
Employee
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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