Return T/F if values match on two filtered tables

[Anonymous]

Hello PowerBI Community, 

I would like to find out how to create an ACTIVE/INACTIVE column on Table U on whether or not the same user ID appears in Table A and vice versa. The difficulty is that I need to create a variety of filters on each of the tables in order to get the lists that I need to match. Also, I have a date table (_dates) connected (using inactive relationship - so I use USERELATIONSHIP) to 'Reference Date' so would like to use that too. 

Essentially...

If Table A [X ID] has Sum of Amount > £0, and the _date[date] is 18/06/2020 (variable), and matches Table U [UserID] (where the First X Application Completed Date is not blank and Is X (T/F) = TRUE), then "ACTIVE".

AND

If Table U [User ID], (where the First X Application Completed Date is not blank and Is X (T/F) = TRUE) is not amongst the Table A[X ID]s , then "INACTIVE". 

I've attached a photo to explain what i'm trying to do...

Thanks,

Simon

@BhaveshPatel

@Greg_Deckler

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create a calculated column like this:

result =
VAR _sum =
    CALCULATE (
        SUM ( 'Table A'[Sum of Amount] ),
        USERELATIONSHIP ( 'Date'[Date], 'Table A'[Reference Date] )
    )
RETURN
    IF (
        _sum > 0
            && 'Table U'[First X Application Completed Date] <> BLANK ()
            && 'Table U'[Is X(T/F)] = TRUE (),
        "Active",
        "Inactive"
    )

Essentially the result will be simliar with as @ ryan_mayu mentioned: 

Sample file is attached that you can refer: Return T/F if values match on two filtered tables.pbix 

Best Regards,
Yingjie Li

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

[Anonymous]

Hi @v-yingjl , 

Many thanks for your suggestion. The last part looks like it can work well, although I realise I am getting errors when trying to CALCULATE & SUM as I am using Direct Query. Would you have any suggestions on how to combat this?

[v-yingjl]

Hi @Anonymous ,

Could you share some screen shots like formula... for discussion?

Or you can consider the formula compatibility under direct query mode by this document:

DAX formula compatibility in DirectQuery mode 

Best Regards,
Yingjie Li

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

[Anonymous]

Hi @v-yingjl,

Many thanks for your reply, please see below:

Just to reiterate, I'm using Direct Query and trying to find out how many X IDs from Table A match User IDs from Table U (using the filters as displayed on the filter panel. I'm also trying to work out vice versa (how many User IDs from Table U match X IDs from table A). 

thanks!

[v-yingjl]

Hi @Anonymous ,

In Direct Qurey mode, aggregate function is not supported in calculated column. You can try to convert them into a measure like this and try to use a table visual to show the result.

result = 
var _sum = 
CALCULATE(
    SUM('Table A'[Sum of Amount]),
    USERELATIONSHIP('Date'[Date],'Table A'[Reference Date])
)
return
IF(
    _sum > 0 && SELECTEDVALUE('Table U'[First X Application Completed Date]) <> BLANK() && SELECTEDVALUE('Table U'[Is X(T/F)]) = TRUE(),
    "Active",
    "Inactive"
)

Best Regards,
Yingjie Li

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

[ryan_mayu]

@Anonymous 

Please see if this is what you want.

Table

Column = if(SUMX(FILTER('Table','Table'[ID]='Table (2)'[ID]&&'Table (2)'[date]='Table'[date]),'Table'[value])>0,True)

[Anonymous]

Hi @ryan_mayu ,

Many thanks for your reply!

I'm afraid that didn't work as when writing the DAX the only values that showed up for Table A were custom measures and there were also issues at the start with SUMX & FILTER.

This is what I put: 

CUSTOM COLUMN =

if(SUMX(FILTER('Table U','Table U'[User ID] = 'Table A'[X ID]&&'Table A'[Reference Date] = '_dates'[date]), 'Table A'[X ID])>0, TRUE)

Also shouldn't I write another formula to find vice-versa?

[ryan_mayu]

@Anonymous 

Could you please share the sample data?

[Anonymous]

@ryan_mayu 

Thank you.

https://docs.google.com/spreadsheets/d/e/2PACX-1vQ1i1iOPkB0LBT9jqoT4I-zEX77IVws8R80As9xrekaYvvRp65Ay...

Do let me know if that link doesn't work...

you'll find that on I have already filtered the reference date on Table A as otherwise I would have way too many dates listed on there. However, the idea remains to maintain USERELATIONSHIP to intuitively use only _dates[date].

[ryan_mayu]

@Anonymous 

Is this what you want? I am not quite clear about your request. Did you build relationship between two tables or use userelationship function?

T/F = 
VAR _sum=CALCULATE(SUM(TableA[Sum of Amount ()]),FILTER(TableA,TableA[X ID]=TableU[User ID]))
return if (_sum>0,True,False)