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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Kolumam
Post Prodigy
Post Prodigy

Grouping Values

Hi All, 

 

I have a Number column from 1-20 but I am not able to group like

 

1-10

1-15

1-20

 

Please help.

 

Grouping.JPG

1 ACCEPTED SOLUTION

Hi @Kolumam,

Try the following steps

 

1. Import your data set

2. Create 2 more references of your data set

3. Now you will have 3 copies of your data. 

4. Filter the data sets as follows

 

Filter your 1st data set such that you have data for those records where the number column will have values between 1 and 10,

2nd data set will have  data for those records where the number column will have values between 1 and 15,

3rd data set will have data  for those records where the number column will have values between 1 and 20,

 

5. Now create a static column.

In the 1st data set, this column should have 1-10 as the data

In the 2nd data set, this column should have 1-15 as the data 

In the 3rd data set, this column should have 1-20 as the data

 

6. Now append (union) these 3 queries

7. After appending, you will have a single dataset will 3 sets of similar data

8. Now use distinct values of this new column in your slicer selection.

9. Your visual should have data from this new data set

10. Changing Selections in the slicer will not filter the data for the groups

 

But this has couple of limitations.

1. Atleast one group in the slicer should always be selected. If not selected, the all the 3 copies will be displayed, which is not right

2. This will result in multiple copies of the same data. But this will not affect performance as we are using reference

 

In case of queries, let me know. Will try to help

 

Hope this solves your requirement!!!

 

View solution in original post

19 REPLIES 19
Thejeswar
Resident Rockstar
Resident Rockstar

Hi @Kolumam,

You got solution for this?

 

Also can you tell if the number column will have only upto 20 as a static limit or will it have more values as well?

Thejashwar,

 

I haven't got a solution for this.

 

It's just upto 20. How do I have 3 filters as 1-10, 1-15 & 1-20 so that people can click on the 3 filters and see the data. Currently I am getting others. Grouping doesn't work

Hi @Kolumam,

Try the following steps

 

1. Import your data set

2. Create 2 more references of your data set

3. Now you will have 3 copies of your data. 

4. Filter the data sets as follows

 

Filter your 1st data set such that you have data for those records where the number column will have values between 1 and 10,

2nd data set will have  data for those records where the number column will have values between 1 and 15,

3rd data set will have data  for those records where the number column will have values between 1 and 20,

 

5. Now create a static column.

In the 1st data set, this column should have 1-10 as the data

In the 2nd data set, this column should have 1-15 as the data 

In the 3rd data set, this column should have 1-20 as the data

 

6. Now append (union) these 3 queries

7. After appending, you will have a single dataset will 3 sets of similar data

8. Now use distinct values of this new column in your slicer selection.

9. Your visual should have data from this new data set

10. Changing Selections in the slicer will not filter the data for the groups

 

But this has couple of limitations.

1. Atleast one group in the slicer should always be selected. If not selected, the all the 3 copies will be displayed, which is not right

2. This will result in multiple copies of the same data. But this will not affect performance as we are using reference

 

In case of queries, let me know. Will try to help

 

Hope this solves your requirement!!!

 

Hi Thejeshwar,

 

The bar chart is also adding the duplicates.

The dataset that you mention is 3 seperate tables?

 

I am using a column header as a filter by using a selectedvalue function. Does it impact that?

Stachu
Community Champion
Community Champion

you can get the result you want if you force the filter in the measure you're displaying - this does mean that you will need to do it in every single one of them



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hi Stachu,

 

Can you please explain clearly?

Stachu
Community Champion
Community Champion

1) create a table named Slicer in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ1NFDSUTIEYQOlWB2wiClMxBQqYgRTA2TExgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [NrOfYears = _t, Min = _t, Max = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NrOfYears", type text}, {"Min", Int64.Type}, {"Max", Int64.Type}})
in
    #"Changed Type"

2) load it to the model
3) in the model create a new measure

Measure_filter = 
VAR year_min = SELECTEDVALUE(Slicers[Min],MIN(Slicers[Min]))
VAR year_max = SELECTEDVALUE(Slicers[Max],MAX(Slicers[Max]))
RETURN
CALCULATE(SUM(Savings[Value]),KEEPFILTERS(Savings),Savings[Year]>=year_min && Savings[Year] <= year_max) 

where the blue syntax would be your original KPI
4) create a slicer based on Slicer[NrOfYears], and put [Measure_filter] in the view
Slicer now filters data to only show for the years from defined threshold - for this measure, if you have other measures that would have values for remaining years you would need to rewrite them in similar manner



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Hi Stachu,

 

The issue is I have 3 headers which I am using as a slicer. If I put the measure for years, the other measure doesn't sync. See below.

Savings Selector = 
DATATABLE(
    "Index", INTEGER,
    "MyColumns", STRING,
    {
        { 1, "Flat" },
        { 2, "Escalating" },
        { 3, "Discounted"}
    })
Solar Cost = 
VAR selectedcolumn =
    SELECTEDVALUE ( 'Savings Selector'[Index] )
RETURN
    SWITCH (
        TRUE (),
        selectedcolumn = 1, SUM ( Savings[Solar Cost (flat)] ),
        selectedcolumn = 2, SUM (Savings[Solar Cost (escalating)] ),
        selectedcolumn = 3, SUM (Savings[Solar Cost (discounted)]))

Yes you will have three separate tables initially. But after appending you will have only one table at the end.

 

Do all that you have done now with the existing dataset using this new table

Anonymous
Not applicable

Create a grouping table.  Make it contain at least 2 columns.  First column is the number you will find in your data.  Each other column will be the grouping you want to use.  For example it might look like:

Headers:  Value, GroupA, GroupB, GroupC
Row1:  1, "0-10", "0-15", "0-20"
...
...
Row16: 16, "11-20", "16-30", "0-20".

 

 

Now join your existing data table to this reference table via the value field.

Hi @Anonymous @Zubair_Muhammad

 

How do I get rid of the 2nd row in all the groups? I don't need 11-20, 16-20 

dasdad.JPG

Anonymous
Not applicable

@Kolumam  My table was an example.  I assume you know your groupings and would create approprately.

Hi @Anonymous, @Zubair_Muhammad

 

The issue is if I make group B for years 1 to 15 as 1-15 and leave out the rows 16 to 20 as blank...when I add the slicer it is showing another option as blank

 

Eg. Group B 

      1-15

       Others

 

How do I fix this issue?

Anonymous
Not applicable

If you are creating a report around group B, create a page based filter that excludes everything except Group B 1 - 15.

 

If you are trying to create a report with Group A and Group B on the same report, you are mixing contexts and they will conflict.

Is there any solution to display both 1-10 and 1-15 as slicers without any conflicts?

Anonymous
Not applicable

The slicers show the line items in the table for the page context.  So if a page wants to exclude a row for a certain slicer then it will be excluded for the other slicer too.    The solution you need is simply what you previously suggested.  The unwanted categories should be listed as "Other" or some moniker to suggest they shouldn't be selected, but not actively preventing someone from doing so.

 

This will mean your slicers will act like "I want rows that conform with my Group A selection and also conform to my Group B selection" (if you make a selection from both).  If only a single slicer is selected, then you get the rows associated with that single selection.

@Kolumam

 

Also you can try a calculated column on this pattern

 

Group Column =
SWITCH (
    TRUE (),
    Table1[Numbers] <= 10, "1-10",
    Table1[Numbers] <= 20, "11-20",
    "Above 20"
)

Regards
Zubair

Please try my custom visuals

Hi Zubair,

 

Group Name = 
SWITCH (
    TRUE (),
    
         Savings[Year] <= 10, "10 Year",                                                                                                                                                                                  
         Savings[Year] <= 15, "15 year",

         Savings[Year] <= 20, "20 year")

I was using the above formula but the issue is when the user clicks on "15 year", the graph just shows years from 11 to 15. See below pic when I click on 15 year. How do I make it to display from 1 to 15?

savings.JPG

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.