Forum Discussion

Elliott's avatar
Elliott
Advocate II
10 years ago
Solved

Group by Data Ranges

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)...
Need Help
  • konstantinos's avatar
    konstantinos
    10 years ago

    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

konstantinos's avatar
konstantinos
Memorable Member
10 years ago

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's avatar
vanessa
Post Patron
10 years ago

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.

  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    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...

  • konstantinos's avatar
    konstantinos
    Memorable Member
    10 years ago

    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 ) )
)
  • konstantinos's avatar
    konstantinos
    Memorable Member
    10 years ago

    vanessa Can you post your formula?

    Did you create the slicer table?

  • vanessa's avatar
    vanessa
    Post Patron
    10 years ago

    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.

  • vanessa's avatar
    vanessa
    Post Patron
    10 years ago

    Sean konstantinos

     

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