Forum Discussion

Elliott's avatar
Elliott
Icon for Advocate II rankAdvocate 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

Anonymous's avatar
Anonymous
Not applicable
10 years ago

konstantinos

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

konstantinos's avatar
konstantinos
Icon for Memorable Member rankMemorable Member
10 years ago

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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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's avatar
      konstantinos
      Icon for Memorable Member rankMemorable Member
      10 years ago

      Anonymous

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        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?