Forum Discussion

bijaymaharjan's avatar
bijaymaharjan
Icon for Helper I rankHelper I
8 months ago
Solved

DAX SOLUTION NEEDED

Hi all,  Below is my requirement and I keep getting incorrect results. Hoping someone can help me out.  I have 2 tables, Policy table has PolicyNumber, Term, TransactionNum, TransactionType, Policy...
  • bijaymaharjan's avatar
    bijaymaharjan
    8 months ago

    Hi Ahmed-Elfeel ,
    I had to make a small tweak in your query to make it work. However, thank you for providing a solid base query for the result. Here is the final query:

    another_try_v2 =
    VAR _max_date =
    CALCULATE(
    MAX(
    'DateTable'[Date]
    ),
    ALLSELECTED(
    'DateTable'
    )
    )

    VAR _filterd_as_of_max =
    FILTER(
    --ALLSELECTED(
    'Policy',
    --),
    'Policy'[AccountingDate] <= _max_date
    )

    VAR _max_trx_per_policy =
    SUMMARIZE(
    _filterd_as_of_max,
    'Policy'[PolicyNumber],
    'Policy'[Term],
    "Max_seq", MAX(
    'Policy'[TransactionNum]
    )
    )

    VAR _valid_policies =
    FILTER(
    ADDCOLUMNS(
    _max_trx_per_policy,
    "TrxType",
    VAR _PolicyNumber = [PolicyNumber]
    VAR _term = [Term]
    VAR _max_seq = [Max_seq]
    RETURN
    CALCULATE(
    SELECTEDVALUE(
    'Policy'[TransactionType]
    ),
    _filterd_as_of_max,
    'Policy'[PolicyNumber] = _PolicyNumber,
    'Policy'[Term] = _term,
    'Policy'[TransactionNum] = _max_seq
    ),
    "ExpDate",
    VAR _PolicyNumber_2 = [PolicyNumber]
    VAR _term_2 = [Term]
    VAR _max_seq_2 = [Max_seq]
    RETURN
    CALCULATE(
    MAX(
    'Policy'[PolicyExpDate]
    ),
    _filterd_as_of_max,
    'Policy'[PolicyNumber] = _PolicyNumber_2,
    'Policy'[Term] = _term_2,
    'Policy'[TransactionNum] = _max_seq_2
    )
    ),
    [TrxType] <> "CA" && [ExpDate] >= _max_date
    )

    RETURN
    SUMX(
    _valid_policies,
    VAR _p = [PolicyNumber]
    VAR _t = [Term]
    RETURN
    CALCULATE(
    SUM(
    'Policy'[WrittenPremium]
    ),
    _filterd_as_of_max,
    'Policy'[PolicyNumber] = _p,
    'Policy'[Term] = _t
    )
    )

FBergamaschi's avatar
FBergamaschi
Icon for Super User rankSuper User
8 months ago

I see a lot of bad DAX syntax and practices in the DAX you were given

 

-SUMMARIZE in which calculated columns are included in the SUMMARIZE call itself (nobody should ever do this)

-reference to columns without table reference included

-filtering tables when a few columns are enough

- SUMMARIZE to group a virtual table

...

 

As a human, I will give you DAX code written by myself that avoids pitfalls that AI is unable to detect and that guarantees you have a valid calculation all the time, it might need a few fixes but this is what a good (human) DAX developer should start from:

 

SolutionMeasure =
VAR MaxDate =
MAX( 'DateTable'[Date] )

 

VAR FilteredPol =
FILTER( ALL ( Policy[AccountingDate] ), Policy[AccountingDate] <= MaxDate )

 

VAR MaxTransPerPolicy =
ADDCOLUMNS(
SUMMARIZE(
ALL(Policy),
Policy[PolicyNumber],
Policy[Term]
),
"@MaxTrans", CALCULATE ( MAX( Policy[TransactionNum] ) )
)

 

VAR ValidPolicies =
FILTER(
ADDCOLUMNS(
MaxTransPerPolicy,
VAR CurrT = Policy[Term]
VAR CurrPolicy = Policy[PolicyNumber]
RETURN
"@TransType",
CALCULATE(
SELECTEDVALUE( Policy[TransactionType] ),
FilteredPol,
Policy[PolicyNumber] = CurrPolicy,
Policy[Term] = CurrT,
Policy[TransactionNum] = [@MaxTrans]
),
"@ExpDate",
CALCULATE(
MAX( Policy[PolicyExpDate] ), -- this might be subsituted by SELECTEDVALUE ( Policy[PolicyExpDate] ) as I am unsure on a few details
FilteredPol,
Policy[PolicyNumber] = CurrPolicy,
Policy[Term] = CurrT,
Policy[TransactionNum] = [@MaxTrans]
)
),
[@TransType] <> "CA" && [@ExpDate] >= MaxDate
)

RETURN
SUMX(
ValidPolicies,
Policy[WrittenPremium] ,
)

 

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI