Forum Discussion

tbutusmyo's avatar
tbutusmyo
Frequent Visitor
3 years ago
Solved

One slider for multiple columns

I am currently trying to use a single number "between" slider to designate upper and lower bound parameters for 4 values to a shared Group ID (ie: a container filled with 4 different liquids). However, I am having trouble getting this to work pivoted or unpivoted.

 

If I have the data pivoted, I can use a single slider, but when put into a table (Columns are: Group ID, Value 1, Value 2, Value 3, Value 4 or to follow the previous analogy: Container ID, Liquid 1, Liquid 2, Liquid 3, Liquid 4) the numbers for each value dissapear from the table individually as dictated by the slider and not by the group representing all 4 values. In other words the bounds work, but the result is gaps within the reference table instead of eliminating a row entirely. With the goal being to have a Group elminated if a single value falls outside of the adjusted bounds, it falls a little short.

 

The issue with pivoting the data is that the 4 values are broken out into distinct columns, which then require their own individual sliders to set the upper and lower bounds for the data. While this method does meet the goal of eliminating the rows from a reference table when a sing it also dele values falls out of bounds,feats the intent of having a single slider to dictate the bounds.

 

Sample data and pbi file here:

https://drive.google.com/drive/folders/1b6Rpp7FDpZJv2snXpY_Pyxx0BY3znEpA?usp=share_link

 

Not sure how to go about it.

 

Note in the image the unpivoted has blank spaces, where-as the pivoted on the right has elminated a row entirely off of one value.

 

Unpivoted Data

Group IDLiquid IDValue
1234510.2
1234520.3
1234530.4
1234540.5
2234510.6
2234520.7
2234530.8
2234540.9
3234511
3234521.1
3234531.2
3234541.3
4234511.4
4234521.5
4234531.6
4234540.2
5234510.3
5234520.4
5234530.5
5234540.6
6234510.7
6234520.8
6234530.9
6234541
7234511.1
7234521.2
7234531.3
7234541.4
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi tbutusmyo ,

    I updated your sample pbix file(see Page 2 in the attachment), please check if that is what you want.

    1. Create a what-if parameter

    Parameter = GENERATESERIES(0, 2, 0.01)

    2. Create two measures as below

    Sum of Value = SUM('Unpivoted'[Value])
    Measure =
VAR _minvalue =
    MIN ( 'Parameter'[Parameter] )
VAR _maxvalue =
    MAX ( 'Parameter'[Parameter] )
VAR _selgroup =
    SELECTEDVALUE ( 'Unpivoted'[Group ID] )
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Unpivoted'[Group ID] ),
        FILTER (
            ALLSELECTED ( 'Unpivoted' ),
            [Sum of Value] < _minvalue
                || [Sum of Value] > _maxvalue
        )
    )
RETURN
    IF ( _selgroup IN _tab, BLANK (), [Sum of Value] )

    3. Create a matrix visual

    Best Regards

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    The image you have posted is not clear and neither is your question.  Share some data and show the expected result.

    • tbutusmyo's avatar
      tbutusmyo
      Frequent Visitor
      3 years ago

      Updated the picture and added sample data. Not sure what you mean by showing the expected result other than what I have already described in text and now in text in the image. The closest to the expected result being shown would be the slider from the unpivoted table used in conjunction with the table from the pivoted table.

       

      One way I was thinking of acccomplshing that was to create some form of column that would take the max value for a given group ID from the unpivoted table and use that to filter out the values. However, I was unable to figure out how I would do that.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi tbutusmyo ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    If the above one can't help you get the expected result, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    • tbutusmyo's avatar
      tbutusmyo
      Frequent Visitor
      3 years ago

      Added link to include the sample data and example pbi file.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi tbutusmyo ,

        I updated your sample pbix file(see Page 2 in the attachment), please check if that is what you want.

        1. Create a what-if parameter

        Parameter = GENERATESERIES(0, 2, 0.01)

        2. Create two measures as below

        Sum of Value = SUM('Unpivoted'[Value])
        Measure =
VAR _minvalue =
    MIN ( 'Parameter'[Parameter] )
VAR _maxvalue =
    MAX ( 'Parameter'[Parameter] )
VAR _selgroup =
    SELECTEDVALUE ( 'Unpivoted'[Group ID] )
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Unpivoted'[Group ID] ),
        FILTER (
            ALLSELECTED ( 'Unpivoted' ),
            [Sum of Value] < _minvalue
                || [Sum of Value] > _maxvalue
        )
    )
RETURN
    IF ( _selgroup IN _tab, BLANK (), [Sum of Value] )

        3. Create a matrix visual

        Best Regards