Power Bi Dax measure to send data to different table visuals based on data from data source

[anandmitta]

I have 2 tables from a data source

Table A(Data Source)

Name Age

A	10
B	12
C	13
D	14
E	15
F	16
G	17

Table B(Data Source)

Name DOB

A	10-MAR
C	20-APR
D	31-MAY
G	26-DEC

I have 2 table Visuals in power bi

• Table 1(Visual)
• Table 2(Visual)

I have go through all the Name fields in Table A(Data source) and check if that Name is in Table B(Data Source)

If the name is present in Table B (Data Source) send that name to Table 1 (Visual) else send that to Table 2 (Visual)

I'm finding difficulty in creating DAX measure to make this work in power bi.

[mahoneypat · Accepted solution]

Here you go.  I put them in the same table visual with the TableA[Name] column, but you can duplicate the table and use one measure in each.

Is In Table B =
VAR Brows =
    CALCULATE ( COUNTROWS ( TableB ), TableB[Name] IN VALUES ( TableA[Name] ) )
RETURN
    IF ( Brows > 0, "Y" )

Is Not In Table B =
VAR Brows =
    CALCULATE ( COUNTROWS ( TableB ), TableB[Name] IN VALUES ( TableA[Name] ) )
RETURN
    IF ( ISBLANK(Brows), "Y" )

Pat

[mahoneypat]

This is doable with two DAX measures, but why not just merge the two tables.  Where a name exists in B, you'll have data in those columns expanded from B, and where it doesn't you won't.  You can then make two table visuals and use a visual filter to either show the blank values or everything but the blank values.

Pat

[anandmitta]

Thanks for that Pat, but the data comes from 2 different sources and I'm loading the data using DirectQuery rather than Import. When trying to merge those tables I get the error "This step result in a query that is not supported in DirectQuery mode". So I thought using DAX would be appropriate solution for this and was facing difficulty in creating those DAX measures.

If possible could you please help me with those DAX measures?