Forum Discussion

imadelmouden's avatar
imadelmouden
Frequent Visitor
8 years ago
Solved

Count rows by filtering from two tables

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 scri...
Need Help
Show and Tell
Tips and Tricks
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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.