CODEWITHSUNDEEP
t-sqlpowerbidaxpowerbi-desktop
Serdia
Serdia

Reputation: 4428

How to translate SQL to DAX, Need to add FILTER

I want to create calculated table that will summarize In_Force Premium from existing table fact_Premium.

How can I filter the result by saying:

TODAY() has to be between `fact_Premium[EffectiveDate]` and (SELECT TOP 1 fact_Premium[ExpirationDate] ORDE BY QuoteID DESC)

In SQL I'd do that like this:

    `WHERE CONVERT(date, getdate()) between CONVERT(date, tblQuotes.EffectiveDate) 
and (
      select top 1 q2.ExpirationDate 
        from Table2 Q2 
        where q2.ControlNo = Table1.controlno 
        order by quoteid` desc
       )

Here is my DAX statement so far: 

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)
    )

Also, what would be more efficient way, to create calculated table from fact_Premium or create same table using sql statement (--> Get Data--> SQL Server) ?

Upvotes: 0

Views: 172

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35613

There are 2 potential ways in T-SQL to get the next effective date. One is to use LEAD() and another is to use an APPLY operator. As there are few facts to work with here are samples:

select *
from (
      select *
      , lead(EffectiveDate) over(partition by CompanyGUID order by quoteid desc) as NextEffectiveDate
      from Table1
      join Table2 on ...
     ) d

or 

select table1.*, oa.NextEffectiveDate
from Table1
outer apply (
    select top(1) q2.ExpirationDate AS NextEffectiveDate
    from Table2 Q2 
    where q2.ControlNo = Table1.controlno 
    order by quoteid desc
     ) oa

nb. an outer apply is a little similar to a left join in that it will allow rows with a NULL to be returned by the query, if that is not needed than use cross apply instead.

In both these approaches you may refer to NextEffectiveDate in a final where clause, but I would prefer to avoid using the convert function if that is feasible (this depends on the data).

Upvotes: 1

Related Questions