Need to convert this SQL query to DAX

[Anonymous]

Hi All,

         Can you please help on how to convert the below SQL query to DAX. Thanks in advance.

select distinct b.polid,c.TranType,

case when c.TranType='XLC' then

(select a.commamt from [VoyagerT100].[AFW_Invoice] a where a.polid=b.polid and

a.description= 'Commercial Property - New business'

and [Commission Person]='Agency')

when c.TranType='REI' then

(select a.commamt from [VoyagerT100].[AFW_Invoice] a where a.polid=b.polid and

a.description= 'Commercial Property - Cancellation confirmation'

and [Commission Person]='Agency')

end as originalcommission,

case when c.TranType='XLC' then

(select a.commamt from [VoyagerT100].[AFW_Invoice] a where a.polid=b.polid and

a.description= 'Commercial Property - Cancellation confirmation'

and [Commission Person]='Agency')

when c.TranType='REI' then

(select a.commamt from [VoyagerT100].[AFW_Invoice] a where a.polid=b.polid and

a.description= 'Commercial Property - Reinstatement'

and [Commission Person]='Agency')

end as Cancellcommission

from [VoyagerT100].[AFW_BasicPolicy] b

join [VoyagerT100].[AFW_PolicyTrans] c on b.polid=c.polid

join [VoyagerT100].[AFW_Invoice] d on c.poltpid=d.poltpid

where b.polid='7F08AFFE-0D43-4FE0-BF62-250C18C03505'

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

Based on the SQL statement you gave, assume that you have the three tables([VoyagerT100].[AFW_BasicPolicy], [VoyagerT100].[AFW_PolicyTrans] and [VoyagerT100].[AFW_Invoice]) mentioned in the SQL statement in your data model and the relationships between the tables have been created as shown in the figure below. Then you can create the following measures to get the originalcommission and Cancelcommission based on different conditions:

originalcommission =
VAR _selpolid = '[VoyagerT100].[AFW_BasicPolicy]'[polid]
VAR _seltranstype =
    SELECTEDVALUE ( '[VoyagerT100].[AFW_PolicyTrans]'[TranType] )
VAR _xlc =
    CALCULATE (
        MAX ( '[VoyagerT100].[AFW_Invoice]'[commamt] ),
        FILTER (
            '[VoyagerT100].[AFW_Invoice]',
            [polid] = _selpolid
                && [description] = "Commercial Property - New business"
                && [commission person] = "Agency"
        )
    )
VAR _rei =
    CALCULATE (
        MAX ( '[VoyagerT100].[AFW_Invoice]'[commamt] ),
        FILTER (
            '[VoyagerT100].[AFW_Invoice]',
            [polid] = _selpolid
                && [description] = "Commercial Property - Cancellation confirmation"
                && [commission person] = "Agency"
        )
    )
RETURN
    SWITCH ( _seltranstype, "XLC", _xlc, "REI", _rei )

Cancellcommission =
VAR _selpolid = '[VoyagerT100].[AFW_BasicPolicy]'[polid]
VAR _seltranstype =
    SELECTEDVALUE ( '[VoyagerT100].[AFW_PolicyTrans]'[TranType] )
VAR _xlc =
    CALCULATE (
        MAX ( '[VoyagerT100].[AFW_Invoice]'[commamt] ),
        FILTER (
            '[VoyagerT100].[AFW_Invoice]',
            [polid] = _selpolid
                && [description] = "Commercial Property - Cancellation confirmation"
                && [commission person] = "Agency"
        )
    )
VAR _rei =
    CALCULATE (
        MAX ( '[VoyagerT100].[AFW_Invoice]'[commamt] ),
        FILTER (
            '[VoyagerT100].[AFW_Invoice]',
            [polid] = _selpolid
                && [description] = "Commercial Property - Reinstatement"
                && [commission person] = "Agency"
        )
    )
RETURN
    SWITCH ( _seltranstype, "XLC", _xlc, "REI", _rei )

Then create a table visual: put the field [VoyagerT100].[AFW_PolicyTrans].[Polid] , [VoyagerT100].[AFW_Invoice].[Trantype] and the above these new measures onto the table visual.

Best Regards

[Jorenvdl]

Wy do you want to convert in to DAX?
Other option would be to convert it in a Power Query

[selimovd]

Hey @Anonymous ,

why do you want to convert it do DAX?

You can also connect to your source directly with the query. Just add it at the SQL statement:

Like this you have the table exactly how you need it.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

@selimovd  Thank you, but i need to convert this into DAX. Can you please help on this.

[selimovd]

@Anonymous 

Why do you need to convert that in DAX?

Then if you need to convert that into DAX, how does the data you import look like?