#powerbi #dax
#powerbi #dax
Вопрос:
Я начал изучать некоторые основы использования Power BI и нуждаюсь в помощи в решении проблемы.
Пожалуйста, смотрите прилагаемую мою таблицу.
Я хочу вычислить среднее значение процента от последней записи с последним «Статусом 1» для каждого идентификатора. В моей базе данных есть некоторые идентификаторы, в которых статус «Status 1» содержится только один раз (например, ID 4) — я хотел бы исключить эти идентификаторы. Возможно ли это?
Для этого примера это было бы:
(94 82 85) / 3 = 87
Я начал с попытки вычислить среднее значение всех «Процентов» для всего «Статуса 1».
Test Average 2 =
CALCULATE (
AVERAGEX(Tabelle1; Tabelle1[Percent]);
FILTER ( ALL ( Tabelle1 ); Tabelle1[Status]="Status 1" );
ALL (Tabelle1)
)
Как мне поступить? Я не знаю, как создать код с таким количеством условий. Я пытался исследовать решение, но сдался. Я надеюсь, что вы сможете мне помочь и были бы признательны за помощь начинающему программисту 🙂
Заранее спасибо, Дженни
Ответ №1:
Во-первых, добро пожаловать в семейство Power BI!
Во-вторых, вот как я бы подошел к вашей проблеме…
1. Создайте столбец «Дата-время»
Поскольку нам нужно проверить наличие последней записи, и, предположительно, вы хотите, чтобы она проверялась на основе даты и времени, проще, если нужно проверить только один столбец).
На ленте перейдите в раздел Моделирование -> Новый столбец.
В появившейся строке формул введите следующую формулу. Power BI может распознавать столбцы даты и времени такими, какие они есть, и поскольку они оба являются элементами даты / времени, Power BI позволяет нам выполнять простое сложение. - 1
В формуле используется для учета «дня», который связан со временем.
DateTime = [Date] [Time] - 1
2. Создайте среднюю меру
Сначала приведена полная формула, а затем приведена разбивка по частям.
Last Percent Average =
VAR TargetStatus = "Status 1"
VAR IDsWithMoreThanOneStatus1 = FILTER('Data', COUNTROWS(FILTER('Data', IFERROR(SEARCH(TargetStatus, [Status]), -1) > 0 amp;amp; [ID] = EARLIER([ID]))) > 1)
VAR LastStatus = FILTER(IDsWithMoreThanOneStatus1, IFERROR(SEARCH(TargetStatus, [Status]), -1) > 0 amp;amp; [DateTime] >= MAXX(FILTER('Data', IFERROR(SEARCH(TargetStatus, [Status]), -1) > 0 amp;amp; [ID] = EARLIER([ID])), [DateTime]))
RETURN
DIVIDE(SUMX(LastStatus, [Percent]), COUNTROWS(LastStatus), BLANK())
Первая часть измерения — это определение переменных. Переменные очень полезны, и я бы посоветовал вам хотя бы бегло взглянуть на них. Вот только одна статья о них.
Первая переменная просто обозначает целевой статус (на случай, если вы когда-нибудь захотите его изменить). Эта переменная необязательна, как будто она никогда не изменится, вы могли бы просто поместить текст в другие формулы.
VAR TargetStatus = "Status 1"
Вторая переменная фильтрует основной список данных только по тем идентификаторам, у которых более одного «Статуса 1» (исключая ID = 4 в данном случае). Одним из ключей с этой переменной является EARLIER
функция. Несмотря на ужасное название (на мой взгляд), оно чрезвычайно полезно. Вы можете прочитать официальную документацию по этому вопросу здесь.
Шаг за шагом по переменной: мы хотим отфильтровать таблицу данных… когда количество строк больше единицы… когда мы фильтруем таблицу, чтобы включить «Статус 1» для указанного идентификатора.
SEARCH
Функция просмотрит всю строку и сообщит нам начальную позицию нашей поисковой строки (TargetStatus). Нам нужно обернуть это в IFERROR
функцию для обработки случаев, когда TargetStatus не отображается в значении [Status]. Затем мы просто проверяем, не больше ли результат 0, указывающий на то, что TargetStatus находится в значении [Status].
VAR IDsWithMoreThanOneStatus1 =
FILTER(
'Data',
COUNTROWS(
FILTER(
'Data',
IFERROR(SEARCH(TargetStatus, [Status]), -1) > 0 amp;amp;
[ID] = EARLIER([ID])
)
) > 1
)
Третья переменная фильтрует список из второй переменной, чтобы зафиксировать последнее «Состояние 1» для каждого идентификатора. Здесь используется тот же мыслительный процесс, что и для второй переменной: мы хотим отфильтровать таблицу (из второй переменной), где status = «Статус 1» и [DateTime] больше или равно максимальному значению [DateTime] для всех «Статусов 1» для указанного идентификатора.
VAR LastStatus =
FILTER(
IDsWithMoreThanOneStatus1,
IFERROR(SEARCH(TargetStatus, [Status]), -1) > 0 amp;amp;
[DateTime] >= MAXX(
FILTER(
'Data',
IFERROR(SEARCH(TargetStatus, [Status]), -1) > 0 amp;amp;
[ID] = EARLIER([ID])
),
[DateTime]
)
)
RETURN
Ключевое слово просто сообщает Power BI, что мы закончили определять переменные и что все остальное в формуле определяет фактическое значение, возвращаемое для этой меры.
RETURN
Наконец, само среднее значение. В этом случае мы будем использовать SUMX
для суммирования всех значений [Процентов] из третьей переменной, а затем разделим это на количество строк третьей переменной. Я бы всегда рекомендовал использовать DIVIDE
функцию, поскольку она обрабатывает деление на 0 и другие ошибки.
DIVIDE(
SUMX(
LastStatus,
[Percent]
),
COUNTROWS(LastStatus),
BLANK()
)
Когда все сказано и сделано, ваша мера даст желаемый результат.
Комментарии:
1. Большое вам спасибо! Это работает действительно хорошо! Мне все еще трудно понять код, но я работаю над пониманием взаимодействия между командами. Я столкнулся с другой трудностью. Есть ли способ использовать подстановочные знаки для первой переменной? Я заметил в моей таблице данных, что иногда в одной ячейке больше «статуса». Подобная ячейка иногда выглядит следующим образом: «Статус 2, Статус 1, Статус 3», поэтому процент этой строки не будет включен в вычисления. : ( Еще раз спасибо @Joe. Я действительно ценю помощь и работу, которую вы вложили в свой ответ!
2. @Jenny98 Есть ли какой-то конкретный фрагмент кода, о котором вы хотели бы рассказать подробнее? При необходимости я могу отредактировать свой ответ, чтобы предоставить больше контекста. Что касается ячеек с несколькими статусами, моя формула все равно должна работать, поскольку она фильтруется по Status = «Статус 1». Таким образом, даже если статус включает 1, если есть что-то еще, оно будет отфильтровано.
3. У меня это почему-то не работает. 🙁 Если я манипулирую набором данных таким образом, что меняю статус в строках 5 и 21, например, на «Статус 1, Статус 3», то он не включается в вычисления. Мое среднее значение получается 82 вместо 87. Мне просто нужно разобраться в DAX в целом. Я все еще не совсем понимаю, как вы используете FILTER, но ваша помощь уже оказала большую помощь. Я не хочу больше тратить ваше время 🙂
4. @Jenny98 Я более чем рад помочь. Это помогает не только вам, но и другим, и я по ходу дела изучаю новые методы. Все, о чем я прошу, это если мой ответ решит ваш вопрос, пожалуйста, отметьте его как принятое решение.
5. @Jenny98 Я неправильно истолковал ваш первый комментарий; я думал, вы хотите исключить строки с несколькими статусами. Я обновил свой ответ, чтобы учесть это. В принципе, замените
[Status] = TargetStatus
наIFERROR(SEARCH(TargetStatus, [Status]), -1) > 0
везде, чтобы получить то, что вам нужно.