Forum Discussion

Kolumam's avatar
Kolumam
Post Prodigy
8 years ago
Solved

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.  
General Comment
Need Help
Show and Tell
  • Thejeswar's avatar
    Thejeswar
    8 years ago

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

     

Stachu's avatar
Stachu
Community Champion
8 years ago

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

Kolumam's avatar
Kolumam
Post Prodigy
8 years ago

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)]))