Блог

Главная - Вопросы по программированию - Формула DAX для нахождения второго минимума с дополнительными критериями

Формула DAX для нахождения второго минимума с дополнительными критериями

#dax #powerpivot

#dax #powerpivot

Вопрос:

У меня проблема, которая связана с двумя таблицами, одна с некоторыми затратами (таблица останавливается на значении, я ввел ожидаемое значение, которое я хочу видеть в своей явной мере), одна с городами, отсортированными по кластерам.

Мне нужно создать явную меру, которая возвращает второе минимальное значение для данного кластера и расхода.

Таблица затрат

Дата Город Расход Значение Ожидаемое значение
2020 Париж Электричество 1 1
2020 Berlin Электричество 1 1
2020 Лондон Электричество 2 1
2020 НЬЮ-ЙОРК Электричество 0 0
2020 Париж Нагрев 1 4
2020 Berlin Нагрев 4 4
2020 Лондон Нагрев 12 4
2020 НЬЮ-ЙОРК Нагрев 7 7

Кластерная таблица

Город Кластер
Париж Европа
Лондон Европа
Berlin Европа
НЬЮ-ЙОРК Америка

Давайте возьмем ожидаемое значение для [электроэнергии] в [Европе] Кластер. Я получаю 1, поскольку два наименьших значения равны 1. Для отопления я получаю 4, поскольку второе минимальное значение равно 4. (Нью-Йорк возвращает ноль для примера, но кластеры не будут содержать только один город.)

На данный момент у меня есть этот фрагмент кода, который не учитывает тип расходов и кластер :

 2ndMin:=minX(
        topN(countrows(ALL(Costs))-1;ALL(Costs);Costs[Value])
        ;Costs[Value])

Есть идеи, как мне отредактировать код, чтобы включить две спецификации выше?

Спасибо!

Ответ №1:

Это сложно из-за связей для минимального значения. Решение состоит в том, чтобы подсчитать строки с минимальным значением, и если несколько строк возвращают минимальное значение, иначе второе значение будет минимальным

 SecondMin =
VAR CurrentCluster =
    SUMMARIZE( 'Costs', 'Cluster'[Cluster] )
VAR CurrentExpense =
    VALUES( Costs[Expense] )
VAR CostsPerClusterAndExpense =
    CALCULATETABLE(
        Costs,
        CurrentCluster,
        CurrentExpense,
        REMOVEFILTERS( 'Cluster' ),
        REMOVEFILTERS( 'Costs' )
    )
VAR MinValue =
    MINX( CostsPerClusterAndExpense, Costs[Value] )
VAR Min2Value =
    MINX(
        FILTER( CostsPerClusterAndExpense, Costs[Value] > MinValue ),
        Costs[Value]
    )
VAR Result =
    IF(
        COUNTROWS( FILTER( CostsPerClusterAndExpense, Costs[Value] = MinValue ) ) > 1,
        MinValue,
        Min2Value
    )
RETURN
    IF( NOT ISEMPTY( Costs ), Result   0 )

Комментарии:

1. Я сохранил образец pbix, который я использовал на своем github, это прямая ссылка для загрузки github.com/SergioMurru/shared/raw/main /…

2. Это работает отлично. Большое вам спасибо! Единственное, что, похоже, не работает, потому что я не указал и думал, что фильтры выполнят эту работу, это то, что минимум рассчитывается за все годы. Если у меня выбран только один год, я бы хотел, чтобы минимум основывался только на этом году. (необязательно, если выбрано несколько лет, пусть минимум будет для всех выбранных лет). Это большое изменение?

3. На самом деле, осталось решить только необязательную часть. На данный момент я создал переменную ‘currentYear’, аналогичную переменной cluster, и она отлично работает из года в год!

4. Вы могли бы попробовать использовать ALLSELECTED(Затраты [Дата]) вместо ЗНАЧЕНИЙ для текущего года

5. На самом деле, у меня есть полная дата в столбце даты, у меня есть таблица календаря с полем «Год». Итак, я создал переменную с суммированием, как вы сделали для кластеров.