Using MINIMUM on a selected subset of Columns.

[geraintdmorgan]

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 A	School B	School C	School D	School E
John Smith	7.931369	5.225723	1.387373	2.483231	1.754478
Jane Smith	3.301236	7.377228	6.132462	4.286314	0.25431
George Michael	8.335102	6.935329	1.530595	1.087116	4.127606
Dr Dre	4.558907	6.381608	6.04904	0.063971	3.517517
John Lennon	0.591551	0.519604	1.171723	2.275928	3.747795
Bob Dylan	2.32096	0.753937	5.907308	3.829349	0.22503
Taylor Swift	1.374067	4.356616	5.733241	1.444474	1.857546
Ed Sheeran	1.005243	6.280569	6.323898	5.536376	0.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.

[v-yulgu-msft · Accepted solution]

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

[Anonymous]

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.