Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Need to convert this SQL query to DAX

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'

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

5 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    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
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • selimovd's avatar
        selimovd
        Most Valuable Professional
        5 years ago

        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?

  • Jorenvdl's avatar
    Jorenvdl
    Frequent Visitor
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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