Forum Discussion

geraintdmorgan's avatar
geraintdmorgan
New Member
7 years ago
Solved

Using MINIMUM on a selected subset of Columns.

I have the following table that i have put into Power BI to do the following exercise.

Which school is the closest for each pupil, and then count that.

 School ASchool BSchool CSchool DSchool E
John Smith7.9313695.2257231.3873732.4832311.754478
Jane Smith3.3012367.3772286.1324624.2863140.25431
George Michael8.3351026.9353291.5305951.0871164.127606
Dr Dre4.5589076.3816086.049040.0639713.517517
John Lennon0.5915510.5196041.1717232.2759283.747795
Bob Dylan2.320960.7539375.9073083.8293490.22503
Taylor Swift1.3740674.3566165.7332411.4444741.857546
Ed Sheeran1.0052436.2805696.3238985.5363760.669079

I did this by Creating a custom column = List.Min({[School A], [School B], [School C], [School D], [School E]})

 

and then

= Table.AddColumn(#"Inserted Minimum", "Closest School", each if [Minimum] = [School A] then "School A" else if [Minimum] = [School B] then "School B" else if [Minimum] = [School C] then "School C" else if [Minimum] = [School D] then "School D" else if [Minimum] = [School E] then "School E" else "X")

 

This is very liekly inefficient, but using excel skills to do this. Anyways, can get a matrix up which does a count of the results of the last column.

 

But, i want to go further than this. I want to be able to select a subset of the Columns School A/B/C/D/E and run the Minimum measure on only this subset. I am stuck. I have tried using ALLSELECTED but i have got very confused by what that. I think i want to either force the MINIMUM working to work on only slected columns, or else create a new table which only includes the selected columns, and then apply the MINIMUM to that new table.

 

 

 

 

 

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi geraintdmorgan ,

     

    In Query editor mode, unpivot  table as below.

     

    In report view mode, create a measure. Use Matrix to display data.

    Min school =
VAR Min_Value =
    CALCULATE ( MIN ( 'Dataset'[Value] ), ALL ( 'Dataset'[School] ) )
RETURN
    IF (
        ISINSCOPE ( 'Dataset'[School] ),
        BLANK (),
        CALCULATE (
            SELECTEDVALUE ( 'Dataset'[School] ),
            FILTER (
                ALLSELECTED ( 'Dataset' ),
                'Dataset'[Name] = SELECTEDVALUE ( 'Dataset'[Name] )
                    && 'Dataset'[Value] = Min_Value
            )
        )
    )

    Best regards,

    Yuliana Gu

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Probably would be better off selecting the first column and then pivot other rows. This should put your data into tabular format, which makes the DAX and M much easier to write. 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi geraintdmorgan ,

     

    In Query editor mode, unpivot  table as below.

     

    In report view mode, create a measure. Use Matrix to display data.

    Min school =
VAR Min_Value =
    CALCULATE ( MIN ( 'Dataset'[Value] ), ALL ( 'Dataset'[School] ) )
RETURN
    IF (
        ISINSCOPE ( 'Dataset'[School] ),
        BLANK (),
        CALCULATE (
            SELECTEDVALUE ( 'Dataset'[School] ),
            FILTER (
                ALLSELECTED ( 'Dataset' ),
                'Dataset'[Name] = SELECTEDVALUE ( 'Dataset'[Name] )
                    && 'Dataset'[Value] = Min_Value
            )
        )
    )

    Best regards,

    Yuliana Gu