Forum Discussion

Ryan_Karolak's avatar
Ryan_Karolak
New Member
1 year ago
Solved

IF with nested CALCULATE Measure between 2 tables not functioning

I am looking to create a measure which pulls from 2 tables. The tables I have are:

* Building (includes data regarding a building's physical information)

*Compliance (includes data regarding compliance elements within a building).

 

I have a Measure that I use to do overall yes/no compliance as follows:

No = CALCULATE(COUNTA(Compliance[Compliant]),Compliance[Compliant]="no")
 
I was asked to split this information between 3 areas that my organisation manages, so I wrote this:
No London = IF(Building[Designation]="London",CALCULATE(COUNTA(Compliance[Compliant]),Compliance[Compliant]="no"),"")
 
I got an error saying "A single value for column 'Designation' in table 'Building' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result." and that when I mouse over [Designation] it says "cannot find name [Designation]". 
Designation is a collumn in the Building Table which has 3 unique entries.
 
I'm still learning alot about PowerBI would appreciate someone looking over where I may have gone wrong.
 
Kind Regards
Ryan
Need Help
  • Deku's avatar
    Deku
    1 year ago

    CALCULATE(

    COUNTA(Compliance[Compliant]),

    Compliance[Compliant]="no",

    Building[Designation]="London"

    )

     

    This assume there is a relationship between the tables

     

    If you want to learn more about that error look at row and filter context

     

2 Replies

  • Deku's avatar
    Deku
    Icon for Super User rankSuper User
    1 year ago

    CALCULATE(

    COUNTA(Compliance[Compliant]),

    Compliance[Compliant]="no",

    Building[Designation]="London"

    )

     

    This assume there is a relationship between the tables

     

    If you want to learn more about that error look at row and filter context

     

    • Ryan_Karolak's avatar
      Ryan_Karolak
      New Member
      1 year ago

      Thanks for a quick response that has fixed it. I've got a second measure to calculate total compliance within an area will adjust. 

       

      Kind Regards

      Ryan