Forum Discussion
Grouping Values
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 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!!!
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?
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
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
Hi Stachu,
Can you please explain clearly?
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 measureMeasure_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
