Forum Discussion

JChris's avatar
JChris
Helper II
9 years ago
Solved

How to count rows using another related table as filter?

I have two tables that are linked:

 

  1. Software (UniqueID, Name, Vendor)
  2. Status (UniqueID, Status, Description)

 

The link between them is UniqueID. I want to create a Measure where I count the total of rows in Software which the Status.Status is "AUTHORIZED". How can I do that?

 

Need Help
  • malagari's avatar
    malagari
    9 years ago

     

    There are a few ways you can do this, depending on how your relationships directions are set up.

     

    Option 1

     

    MeasureName = 
CALCULATE(
   COUNTROWS(Software),
   FILTER(Software, RELATED(Status[Status]) = "AUTHORIZED)
)

    --- OR ---
    
MeasureNameOptimized = 
CALCULATE( 
   COUNTROWS(
      FILTER(Software, RELATED(Status[Status]) = "AUTHORIZED")
   )
)

     

    Option 2

     

     

    MeasureName = 
CALCULATE(
   COUNTROWS(Software),
   FILTER(Status, Status[Status] = "AUTHORIZED")
)

     

1 Reply

  • malagari's avatar
    malagari
    Continued Contributor
    9 years ago

     

    There are a few ways you can do this, depending on how your relationships directions are set up.

     

    Option 1

     

    MeasureName = 
CALCULATE(
   COUNTROWS(Software),
   FILTER(Software, RELATED(Status[Status]) = "AUTHORIZED)
)

    --- OR ---
    
MeasureNameOptimized = 
CALCULATE( 
   COUNTROWS(
      FILTER(Software, RELATED(Status[Status]) = "AUTHORIZED")
   )
)

     

    Option 2

     

     

    MeasureName = 
CALCULATE(
   COUNTROWS(Software),
   FILTER(Status, Status[Status] = "AUTHORIZED")
)