Как найти последнее значение, упорядоченное по другому столбцу

У меня в таблице MyTable есть столбцы: QuoteID, ControlNo, Premium, ExpirationDate

Мне нужно создать меру, которая захватила бы SUM(Premium), а EffectiveDate должна быть <= Today(), а последняя ExpirationDate (упорядоченная по QuoteID DESC) должна быть >= Today().

Как я могу перевести приведенное ниже заявление на DAX?

В SQL я бы сделал так:

select sum(Premium) as Premium
from MyTable t
where EffectiveDate <= GETDATE() and
      (select top 1 t2.ExpirationDate
       from MyTable t2
       where t2.ControlNo = t.controlno
       order by t.quoteid desc) >= GETDATE()

Как я могу написать это в DAX?

Я пробовал это, но он не работает должным образом:

Premium =
CALCULATE (
    SUM ( fact_Premium[Premium] ),
    FILTER (
        fact_Premium,
        fact_Premium[EffectiveDate] <= TODAY () &&
        TOPN ( 1, ALL ( fact_Premium[ExpirationDate] ), 
                  fact_Premium[QuoteID], ASC ) >= TODAY ()
    )
)

ОБНОВЛЕНИЕ:

Пытаюсь создать вычисляемую таблицу из набора данных fact_Premium, но все еще не уверен, как ее отфильтровать

In_Force Premium = 
    FILTER(
        ADDCOLUMNS(
        SUMMARIZE(
           //Grouping necessary columns
            fact_Premium,
            fact_Premium[QuoteID],
            fact_Premium[Division],
            fact_Premium[Office],
            dim_Company[CompanyGUID],
            fact_Premium[LineGUID],
            fact_Premium[ProducerGUID],
            fact_Premium[StateID],
            fact_Premium[ExpirationDate]
                  ),    
            "Premium", CALCULATE(
                                SUM(fact_Premium[Premium])
                                ),
            "ControlNo", CALCULATE(
                            DISTINCTCOUNT(fact_Premium[ControlNo])
                                  )

   ), // Here I need to make sure TODAY() falls between fact_Premium[EffectiveDate] and (SELECT TOP 1 fact_Premium[ExpirationDate] ORDE BY QuoteID DESC)
    )

powerbi powerbi-desktop dax
person Serdia    schedule 21.09.2018    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Здесь есть пара проблем с DAX.

Во-первых, когда вы используете TOPN, ваше выражение порядка (3-й аргумент) может ссылаться только на строки в таблице, с которой вы работаете (2-й аргумент), поэтому использование только столбца [ExpirationDate] не будет работать. Я думаю, вы, вероятно, захотите fact_Premium вместо ALL ( fact_Premium[ExpirationDate] ).

Во-вторых, функция TOPN возвращает таблицу, а не одно значение, поэтому вам нужно каким-то образом получить доступ к нужному столбцу. Один из вариантов - использовать итератор, например SUMX или MAXX:

MAXX( TOPN(...), fact_Premium[ExpirationDate] )

Вы также можете использовать SELECTCOLUMNS, который приведет к тому, что одна строка, таблица с одним столбцом будет просто значением:

SELECTCOLUMNS( TOPN(...), "ExpirationDate", fact_Premium[ExpirationDate] )

Я не могу гарантировать, что это сработает идеально, но это должно приблизить вас к вашей цели:

Premium =
CALCULATE (
    SUM ( fact_Premium[Premium] ),
    FILTER (
        fact_Premium,
        fact_Premium[EffectiveDate] <= TODAY () &&
        SUMX( TOPN ( 1, fact_Premium, fact_Premium[QuoteID], DESC ),
              fact_Premium[ExpirationDate] )
            >= TODAY ()
    )
)
person Alexis Olson    schedule 21.09.2018
comment
Спасибо, Алексис. Отличное объяснение. К сожалению, количество Premium слишком велико. Я подумал, может быть, я смогу создать отдельную вычисляемую таблицу из набора данных «fact_Premium»? Я разместил ОБНОВЛЕНИЕ - person Serdia; 21.09.2018