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:   Software (UniqueID, Name, Vendor) Status (UniqueID, Status, Description)   The link between them is UniqueID. I want to create a Measure where I count the ...
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")
)

     

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")
)