Help to create a measure with multiple conditions from various tables

[_Regina]

Hi Everyone- here is what I am trying to do

I need to get the number of clients who have a total sales of 1000 dollars during certain time period, their application must have been submitted during certain time period, and they must have been referred during certain time period. My data model has a sales table(transaction), application table, refrral table

I tried the following measure with test dates, but I get the error 

A table of multiple values was supplied where a single value was expected

Could anyone help me with the measure that I am trying to build here.

test_Clients funded with $1000 by March 31 from the Referrals =

VAR _start_date = 

   CALCULATE ( MIN ( '_date unpivot'[Date Key] ) )

VAR _end_date =

  CALCULATE ( MAX ( '_date unpivot'[Date Key] ) )

VAR _transaction =    FILTER('transaction',DATESBETWEEN('transaction'[Processing Date],DATE(2022,10,01),DATE(2022,10,31)))

VAR _application =    FILTER('application','application'[Submit Date] <= DATE(2022,10,01))

VAR _referral =    FILTER('referral', DATESBETWEEN ( referral[Created Date], _start_date, _end_date ))

var net_sales=ADDCOLUMNS(CALCULATETABLE( FILTER(SUMMARIZE('transaction','transaction'[Client ID],"NetSalesbyMarch31",SUM('transaction'[Amount])),[NetSalesbyMarch31]>=1000),KEEPFILTERS(_transaction),KEEPFILTERS(_application)

,KEEPFILTERS(_referral)

),"countclient",1)  

VAR r =

    SUMX(net_sales,[countclient])

RETURN

    r

[Anonymous]

Hi  @_Regina ,

You can modify DAX to the following:

test_Clients funded with $1000 by March 31 from the Referrals =
VAR _start_date =
    MINX ( ALL ( '_date unpivot' ), [Date Key] )
VAR _end_date =
    MAXX ( ALL ( '_date unpivot' ), [Date Key] )
VAR _transaction =
    FILTER (
        ALL ( 'transaction' ),
        DATESBETWEEN (
            'transaction'[Processing Date],
            DATE ( 2022, 10, 01 ),
            DATE ( 2022, 10, 31 )
        )
    )
VAR _application =
    FILTER (
        ALL ( 'application' ),
        'application'[Submit Date] <= DATE ( 2022, 10, 01 )
    )
VAR _referral =
    FILTER (
        ALL ( 'referral' ),
        DATESBETWEEN ( referral[Created Date], _start_date, _end_date )
    )
VAR net_sales =
    ADDCOLUMNS (
        CALCULATETABLE (
            FILTER (
                SUMMARIZE (
                    'transaction',
                    'transaction'[Client ID],
                    "NetSalesbyMarch31", SUM ( 'transaction'[Amount] )
                ),
                [NetSalesbyMarch31] >= 1000
            ),
            KEEPFILTERS ( _transaction ),
            KEEPFILTERS ( _application ),
            KEEPFILTERS ( _referral )
       ),
        "countclient", 1
    )
VAR r =
    SUMX ( net_sales, [countclient] )
RETURN
    r

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

what is "[countclient]" in your case?