Group by Data Ranges

[Elliott]

Hi All,

Need a little assistance with what I presume will require a DAX query.

I currently have a column named 'Days Aging' with the following query: Days Aging = DATEDIFF([Date],TODAY(),DAY)

Simply looking up the date column and assigning a value on how many days it has been since that date.

My source data is always based on the last 30 days,

What I am looking to do is create a simplified slicer to select 'Days Aging' ranges.

For instance, I would like a slicer which has the following options:

Today's data

Last 7 Days

Last 30 Days

When I started writing this post I had in my mind that I would have to query a new column to assign those values in the relevant rows... but looking at that now that method wouldn't be possible as for instance 6 days ago would need to be included within Last 7 Days and Last 30 Days.

Therefore my question is.. is this possible to simplfy a slicer to be able to select the relevant data ranges for;

Today's data

Last 7 Days

Last 30 Days

Thanks all 

[konstantinos · Accepted solution]

@Elliott First you need a table with the text that you need on the slicer with also a column for sorting and getting the value.

Use "Enter Data" to create a table

Period                 Sort
Today's Data            1
Last 7 Days             2
Last 30 Days            3

The table won't have any relantionship with others ( let's call it "Periods")

Create a new measure that will change based on the slicer selection

VariablePeriod =
SWITCH (
    MIN ( Periods[Sort] );
    1; CALCULATE ( [yourmeasure]; FILTER ( Table; Table[Days Aging] = 1 ) );
    2; CALCULATE (
        [yourmeasure];
        FILTER ( Table; Table[Days Aging] > 1 && Table[Days Aging] <= 7 )
    );
    3; CALCULATE ( [yourmeasure]; FILTER ( Table; Table[Days Aging] > 30 ) )
)

 Now add as slicer the Periods field from "Periods Table" and use the [VariablePeriods] for your graphs. 

Notice that if there is no selection on the slicer it will calculate the MIN( Periods[Sort]) which is Today's. You can use MAX if you want to show all data.

*I am writing without pbix open, so maybe some mispelling on formula.

* If you go to powerpivotpro.com and search " disconnected slicer" there are many and great posts on the subject

[vanessa]

@konstantinos

What does [yourmeasure] refer to in your comment? I am very new to DAX, so not sure about the syntax.

[konstantinos]

the name of your measure - replace it with your calculation. 

i.e [Sales] or [Total Clients] etc. 

[vanessa]

Actually I have around 5 values in the slicer and I need to group them into 3 categories and display these 3 categories as the new values of the slicer. Is that possible using this?  

[konstantinos]

@vanessa

Depends but if you are just using a column ( it's values ) as slicer and you want to group the values from 5 to 3 then don't use above.

There are multiple ways to do it.

1. In query mode if you are using the latest April powerBI desktop you can select the column and then add a conditional column with nested if's . The wizard is easy to use.

2. You can use the enter data option and create a table with 2 columns one with the unique values of your current slicer and the other with the group values. Then you can relate this table with the column that you use now. 

Use the new table to create the slicer.

3. You can add a calculated column in your table with a formula sort of :

( replace the column with yours nad the same for the values inside)

Grouped Filters =
SWITCH (
    YourTable[SlicerColumnName];
    "Value1"; "New Group Value1";
    "Value2"; "New Group Value1";
    "Value3"; "New Group Value2";
    "Value4"; "New Group Value2";
    "Value5"; "New Group Value3"
)

Hope that helps

[vanessa]

@konstantinos

The 1st aprroach was helpful. Thank you.

The requirement is a little different.

I have a column with values: 

101

102

103

104

105

Now I want a slicer which will have 3 items (by grouping the above 5 values):

Group1    Group2    All

Group1: 101,102

Group2: 103

All: 101,102,103,104,105

I created a new column using IF and I was able to get the first 2 items: ie. Group1 and Group2. How can i get All as well in the same slicer?

[konstantinos]

@vanessa So you where right, the first solution with the parameter table fits your needs better.

Enter Data to create the slicer table as above and use Groups column as slicer

Groups        Sort
All            1
Group 1        2
Group 2        3

Then create a measure 

Measure =
SWITCH (
    MIN ( SlicerTable[Sort] );
    1;  [yourmeasure];
    2; CALCULATE (
        [yourmeasure];
        FILTER ( Table; OR(Table[Code] = 101) ; Table[Code] = 102 )
    );
    3; CALCULATE ( [yourmeasure]; FILTER ( Table; Table[Code] = 103 ) )
)

 Replace [Yourmeasure] with  calculation you have - i.e sales

Replace SlicerTablewith  table contains the values for the slicer - no relantionship with others ( taht you will enter )

Replace Table[Code] with the column that you have the values 101,102...

Hope it helps

edited - If there is no selection on the slicer the measure will calculate the "All" min sort value = 1 

If you need something else to show default change to MAX()mor the sort order.

[vanessa]

@konstantinos

I tried the code with few syntax changes and got the following error:\

 The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

Also, I am not yet clear about the measure, since based on the slicer selection, I have to filter a table report with multiple columns.

[konstantinos]

@vanessa Can you post your formula?

Did you create the slicer table?

[vanessa]

@konstantinos
Yes I created the table with 2 columns.

Formula:
Measure =
SWITCH (
MIN ( SlicerTable[Sort] ),
1, COUNTA(Table[Col1]),
2, CALCULATE (COUNTA(Table[Col1]),FILTER ( 'Table', OR('Table'[Code] = 101) , 'Table'[Code] = 102 )),
3, CALCULATE ( COUNTA(Table[Col1]), FILTER ( 'Table', 'Table'[Code] = 103 ) )
)

For now, I just used count(col1) as the measure. But, I am still not sure about the measure, as I need to display a table visual with multiple columns based on the slicer value.

[Sean]

@vanessa Look at the picture maybe this will clear things up about the Parameter Table

@konstantinos's Measure is Count in my example and Sales $ is a Measure I created the same way 

Now when you Select in the slicer Group 1 it will ONLY affect these 2 Measures (that were written for this!)

Look how Total Sales is not affected in the table on the right and Categories are not filtered too

So if you have other Measures in the Table you want this Slicer to filter you have to change them

Hope this helps! Let me know...

[vanessa]

@Sean @konstantinos

Thanks alot. That was very helpful. I was able to get the slicer with the required values. 🙂

[konstantinos]

There are some errors in syntax

try below,

There is no issue , you can have all the columns in the table and it will filter the columns based on the Code value

Measure =
SWITCH (
    MIN ( SlicerTable[Sort] ),
    1, COUNTA ( Table[Col1] ),
    2, CALCULATE (
        COUNTA ( Table[Col1] ),
        FILTER ( 'Table', OR ( 'Table'[Code] = 101, 'Table'[Code] = 102 ) )
    ),
    3, CALCULATE ( COUNTA ( Table[Col1] ), FILTER ( 'Table', 'Table'[Code] = 103 ) )
)

[Elliott]

Current workaround is to create 3 separate columns for instance...

Last 7 days writes in every relevant row, leaves all others blank

Last 30 day writes in every relevant row, leaves all others blank

Then create seperate slicers for each column / hide titles etc..etc.. don't show blanks.. then allign next to each other..

Would be better in a single slicer but don't think the functionality is there.. please correct me if I'm wrong!