Count rows by filtering from two tables

[imadelmouden]

I want to count rows from a tableB by feltering from tableA too, in fact the tables has a commun clomuns, PHONE and WEEK.
I want to count rows that have the same PHONE and WEEK, here is the sql script I use in oracle to do that :

select count(B.phone) from tableB B, tableA A
where B.phone = A.phone and B.week = A.week;

[v-yulgu-msft · Accepted solution]

Hi @imadelmouden,

Please add below measure into a card visual.

Measure=
VAR temptable =
    FILTER (
        CROSSJOIN (
            TableA,
            SELECTCOLUMNS ( TableB, "Week2", TableB[Week], "Phone2", TableB[Phone] )
        ),
        [Week] = [Week2]
            && [Phone] = [Phone2]
    )
RETURN
    COUNTX ( temptable, [Phone2] )

Best regards,

Yuliana Gu

[Anonymous]

i have three tables one is fact and two dimension.

i want distinctcount(table1[id]) by filtering table2[effective date] with user selected date range.

could you please suggest me dax for this.

[Anonymous]

Hi @imadelmouden

I assume that you have set up the requied relaionship between Table A and Table B.

Try this measure.

Measure = CALCULATE( COUNT(TableB[Phone]),
                            TableB[Phone]= TableA[Phone],
                            TableB[Week] = TableA[Week]
                            )

Thanks

Raj

[imadelmouden]

@Anonymous when I did what you proposed, I got this error :

[imadelmouden]

I have set a relationship between the two tables by PHONE number, and not the ID, because in the tableB you can find the same phone number in different rows by different week