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 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;

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

5 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      imadelmouden
      Frequent Visitor
      8 years ago

      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

    • imadelmouden's avatar
      imadelmouden
      Frequent Visitor
      8 years ago

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