Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
DAX. Прочерк для пустых ячеек
 
Добрый день, коллеги! Помогите пожалуйста, может быть кто сталкивался. Решения сам найти не смог.

Есть сводная таблица Excel, в ячейках которой часть данных отсутствуют. Их необходимо вывести со значением "-".
Пытаюсь сделать это через меру:
Скрытый текст

При таком варианте вместо "-" почему-то выводится "0".
Формат меры - Общие данные.

При статическом задании любого другого числового значения, например
Скрытый текст
значение выводится.

Как исправить?
 
Тут вопрос не в формате меры, а в описании функции IF. Она не умеет выдавать разнотипные значения в разных ветках вычисления, т.е оба результата должны быть либо текстом, либо числом.
Но можно сделать без если так
и так
Результат будет аналогичный.
Изменено: PooHkrd - 31 Июл 2020 12:25:21
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
в описании функции IF. Она не умеет выдавать разнотипные значения в разных ветках вычисления, т.е оба результата должны быть либо текстом, либо числом.
Спасибо! Не знал!
В сторону DAX  смотрю с учетом того, что эти вещи иногда всплывают и в PowerBI тоже. Поэтому мера - в приоритете.

Попробовал заменить на
Скрытый текст
и как-то аналогично - выдаёт ноль.
Есть ли какие-то функции, поддерживающие разнотипные значения?

Про настройки сводной таблицы - тоже как-то не срабатывает, что еще более странно:

 
Я не вижу скриншоты со сторонних ресурсов. Блокирует файрволл. Прикрепите картинки к сообщению.
Вот горшок пустой, он предмет простой...
 
Во вложении
08.jpg (81.13 КБ)
09.jpg (88.55 КБ)
 
Цитата
PooHkrd написал:
Я не вижу скриншоты со сторонних ресурсов

Мерой никак не получится?

UPD:
Самое интересное, что PowerBI данную формулу с If "прожевал" спокойно.
Правда, при сортировке помещает эти значения перед числовыми и иначе сортировать не хочет.


Интересно, PowerPivot в Excel можно обновить?  :D
Основной вопрос топика это не решило.
10.jpg (39.24 КБ)
Изменено: vaniak - 31 Июл 2020 13:33:42
 
vaniak, ну там у вас был какой-то глюк со сводной,
проще всего вообще сделать так
Код
Мера 2 = CALCULATE(SUM('Table'[Заражений]))+0
и потом так
 
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
Мера 2 = CALCULATE(SUM('Table'[Заражений]))+0

Максим, спасибо!
Сейчас проверю на предмет глюков.

Одна проблема - у меня иногда нулевые значения бывают. Ноль - как значение и прочерк - нет данных.
 
Если не секрет, какой аналитический смысл в вашей задаче несет разница между 0 и "-"?
В любом случае, в Power BI работает же, судя по скрину
Цитата
vaniak написал:
при сортировке помещает эти значения перед числовыми и иначе сортировать не хочет.
потому что любой текст больше чем любое число с точки зрения сортировки. отсортируйте по возрастанию и увидите разницу
F1 творит чудеса
 
Цитата
Максим Зеленский написал: какой аналитический смысл в вашей задаче несет разница между 0 и "-"
Не секрет.
Есть продажи "0" и есть "не предоставившие данные", для слайда = "-". Дальше уже - анализ причин почему не предоставили.
 
Доброе время суток
Цитата
vaniak написал:
для слайда = "-".
Переформулирую вопрос Максима - зачем вам в модели данных Power Pivot этот самый прочерк вместо пустого значения, которое не позволяет вам работать с числовыми данными? Как выводить прочерк же в результатах меры вам Алексей уже показал.
 
Цитата
Андрей VG написал:
зачем вам в модели данных Power Pivot этот самый прочерк вместо пустого значения, которое не позволяет вам работать с числовыми данными?
Полностью с Вами солидарен!
Если получится его вывести без внесения в модель - это очень хороший вариант!

Цитата
Андрей VG написал:
Как выводить прочерк же в результатах меры вам Алексей уже показал.

К сожалению не выводит он прочерк. Ноль он вместо него ставит. Перепроверил сейчас еще раз - картинки прилагаю.

Срабатывает вариант "-", @-. Но как потом от символов этих избавиться?
11.jpg (82.21 КБ)
12.jpg (81.65 КБ)
 
Цитата
vaniak написал:
Если получится его вывести без внесения в модель - это очень хороший вариант!
Вместо - используйте чуть другой символ, удерживая Alt наберите на цифровой клавиатуре 0150 - получите длинное тире — :)
 
Спасибо!
Начинает вырисовываться! Ура!

Осталось только теперь понять как в сортировке делать эти строки в самом низу, а не между нулевых значений...

Ручным перетаскиванием срабатывает. Возможно ли сделать автоматически?
13.jpg (42.76 КБ)
Изменено: vaniak - 31 Июл 2020 15:36:21
 
Цитата
vaniak написал:
Возможно ли сделать автоматически?
Ну если еще только одну меру в качестве ранжирования вставить
Код
SORT:=RANKX(ALL('Таблица1'[Регион]), 
            CALCULATE( IF( ISBLANK(SUM('Таблица1'[Заражений])), -1, SUM('Таблица1'[Заражений]),,Dense ) )
Capture.PNG (15.57 КБ)
Изменено: DrillPipe - 31 Июл 2020 23:17:57
 
Ну и можете еще вот такое в
Цитата
vaniak написал:
.....Их необходимо вывести со значением "-".

Если скучно смотреть на прочерки, можно и так
Код
mTotal:=CALCULATE(IF(ISBLANK(SUM('Таблица1'[Заражений])), "¯\_(ツ)_/¯",SUM('Таблица1'[Заражений]) ) )

В PBI я обычно использую функцию UNICHAR
Capture.PNG (9.52 КБ)
 
Цитата
DrillPipe написал:
В PBI я обычно использую функцию  UNICHAR
UNICHAR(129335) как раз подойдет :)
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
UNICHAR(129335)
Может тогда сразу SVG Measure? :)
 
Добрый день!
PooHkrd, Максим Зеленский, Андрей VG, DrillPipe!
Всем большое спасибо!



Хотел бы резюмировать все варианты, чтобы потом долго не собирать:

Для добавления тире вместо пустых ячеек в сводных таблицах Excel

Вариант 1
1) Включить отображение пустых значений
Параметры сводной таблицы -> Вывод -> Показывать элементы без данных в строках и столбцах
2) Заменить пустые значения на длинное тире.
Макет -> Для пустых ячеек отображать:
При зажатом ALT на NUM-lock клавиатуре 0150.
При указании обычного тире значение приравнивается к нулю.

Вариант 2
Создание меры и отображение в сводной таблице значения этой меры.
В качестве прочерка также требуется использовать длинное тире (Alt+0150)
Код
Мера_Значение:= CALCULATE (IF (ISBLANK (SUM (‘Таблица1’[Значение])); "–"; SUM(‘Таблица1’[Значение]) ) )


И в первом и во втором варианте возможно использование других специальных символов, таких как ¯\_(ツ)_/¯ (опционально)

Ни при первом, ни при втором варианте отображения данных с тире сортировка данных корректно не работает, поскольку Excel воспринимает пустые значения равными нулю и между собой их смешивает.
Для корректного отображения данных возможно введение дополнительной меры ранга и сортировка строк по значению этого ранга без вывода его в презентацию или последующим скрытием данного столбца.

Код
Мера_Ранг:=RANKX(ALL('Таблица1');
            CALCULATE( IF ( ISBLANK (SUM('Таблица1'[Значение])); -1; SUM('Таблица1'[Значение])));
            ;
            ;
            Dense)


Значение ALL в мере создаёт ранг всем строкам таблицы вне зависимости от применённых фильтров.
Для вычисления ранга только в отображаемых значениях параметр меняется на ALLSELECTED.
 
vaniak,
Так как у меня 2016 Excel то некоторые новые функции я не смог применить, в PBI эту конструкцию
Код
CALCULATE (IF (ISBLANK (SUM (‘Таблица1’[Значение])); "–"; SUM(‘Таблица1’[Значение]) ) )

можно заменить на более компактную
Код
CALCULATE(
    COALESCE(
        SUM(‘Таблица1’[Значение]);
        UNICHAR(8212) // это номер в Юникоде для длинного тире
    )
)

Для 2019 или 365 Excel не имею возможности проверить
Изменено: DrillPipe - 3 Авг 2020 15:40:49
Страницы: 1
Читают тему (гостей: 1)
Наверх