Need to convert this SQL query to DAX

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