Forum Discussion

pickslides's avatar
pickslides
Icon for Helper I rankHelper I
4 years ago

Looking up values across different tables

Hi, I have two tables. One is a list of students the other a list of activities. I wish to count (in DAX) the number of distinct activities they have choosen if they are "Co-curirrcular" (Green). I have the output column in yellow and red values.

 

 

 

 

Thanks Q

 

8 Replies

  • pickslides's avatar
    pickslides
    Icon for Helper I rankHelper I
    4 years ago

    A-ha.. I found a typo in the formula. Seems to work now. 

     

    If the result is zero, cells are blank, can I populate with 0 somehow?

     

    Q

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      pickslides 

      Two questions: do you have relationship between the two tables? Are you creating a calculated coumn in the student table?

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      pickslides 

      Yes you can

      Count =
COUNTROWS (
    FILTER (
        Participation,
        Participation[Category] = "Co-curirrcular"
            && Participation[Preferred] = Sudent[Preferred]
    )
) + 0
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi pickslides 

    please try

    Count =
COUNTROWS (
    FILTER (
        Participation,
        Participation[Category] = "Co-curirrcular"
            && Participation[Preferred] = Sudent[Preferred]
    )
)

     

    • pickslides's avatar
      pickslides
      Icon for Helper I rankHelper I
      4 years ago

      This didnt work but it didnt give me an error. Thanks!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  pickslides ,

    Here are the steps you can follow：

    1. Create calculated column.

    TABLE Participation:

     

    Flag1 =
var _flag1=IF(
    'TABLE Participation'[Category]="Co-Curricular Activities",1,0)
return
SUMX(
    FILTER(ALL('TABLE Participation'),
    'TABLE Participation'[Perferred]=EARLIER('TABLE Participation'[Perferred]) &&'TABLE Participation'[Category]=EARLIER('TABLE Participation'[Category]) ),_flag1)

     

    Table Student:

     

    Count =
var _if1=
IF(
   NOT( ISBLANK(
CALCULATE(
    MAX('TABLE Participation'[Perferred]),FILTER(ALL('TABLE Participation'),
    'TABLE Participation'[UID]='Table Student'[UID])))),
    CALCULATE(MAX('TABLE Participation'[Flag1]),FILTER(all('TABLE Participation'),'TABLE Participation'[UID]=EARLIER('Table Student'[UID]))))
    return
IF(
    _if1 =0,BLANK(),_if1)

     

    2. Result:

     

    Best Regards,

    Liu Yang

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