Aggregate a column in Table1 based on Match and No Match values from Table 2

[FMF]

Hi,

I have two tables that are currently linked (Relationship) as one to many. I want to create DAX measures that will help generate Visual 1 and Visual 2. Any advice on how this can be achieved

Floyd

[v-rongtiep-msft · Accepted solution]

Hi @FMF ,

Please have a try.

Create a measure.

KPI_DAX = 
var result_1= CALCULATE(SUM('Table 1'[KPI]),FILTER(ALL('Table 1'),'Table 1'[Company]=SELECTEDVALUE('Table 1'[Company])&&[Check]="Match"))
var result_2=CALCULATE(SUM('Table 1'[KPI]),FILTER(ALL('Table 1'),'Table 1'[Company]=SELECTEDVALUE('Table 1'[Company])&&[Check]="Not Match"))
var answer = IF([Check]="Match",result_1,result_2)
return answer

Best Regards

Community Support Team _ Polly

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

[FMF]

Thanks Polly,

Please can you share the DAX measure formula for [Check]

[FarhanAhmed]

See if this calculated column works 

_Check = 

Var LinkID='Table'[LinkID]

RETURN 

IF(COUNTROWS(FILTER('Table (2)','Table (2)'[LinkID]=LinkID))>0,"Matched","Not Matched")

[FarhanAhmed]

Create a calculated column in your Table 1 something like this 

and use it in your Table Visual

_Check = IF(ISBLANK(RELATED('Table (2)'[LinkID])),"Not Matched","Matched")

[FMF]

Hi Farhan,

Thankyou for the response. However my model is based on DQ. Table 1 is from Google BigQuery and Table 2 is from Postgres. "RELATED" does not work with DQ connections

Floyd

[FMF]

Hi Farhan,

COUNTROWS also does not work in DQ connection

Floyd