Forum Discussion

duesouth's avatar
duesouth
Helper I
1 year ago
Solved

Filter by a Measure

I have created a Power BI Dashboard for a UK secondary school.  We have various behaviour events in our MIS, which include when pupils are sent to the Hub (where they have to do their work in silence...
Need Help
  • v-sathmakuri's avatar
    v-sathmakuri
    1 year ago

    Hi duesouth ,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Above error ( The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression.) can be fixed by replacing it with the below measure. Establish the relation ship between Behaviour  and pupil_data using external ID.

     

    NumberInHub_Column =
    CALCULATE (
        COUNTROWS (
            FILTER (
                RELATEDTABLE ( Behaviour ),
                Behaviour[Behaviour Type] IN {
                    "B7 The Learning Hub",
                    "B7 The Learning Hub - 3 Strikes",
                    "B7 The Learning Hub - Lates",
                    "B7 The Learning Hub - No PROUD Card"
                }
            )
        )
    )
     
    If you have any further questions, please feel free to share them along with a sample output. We're happy to help.
     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thank you!!

v-sathmakuri's avatar
v-sathmakuri
Community Support
1 year ago

Hi duesouth ,

 

Thank you for reaching out to Microsoft Fabric Community.

 

Above error ( The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression.) can be fixed by replacing it with the below measure. Establish the relation ship between Behaviour  and pupil_data using external ID.

 

NumberInHub_Column =
CALCULATE (
    COUNTROWS (
        FILTER (
            RELATEDTABLE ( Behaviour ),
            Behaviour[Behaviour Type] IN {
                "B7 The Learning Hub",
                "B7 The Learning Hub - 3 Strikes",
                "B7 The Learning Hub - Lates",
                "B7 The Learning Hub - No PROUD Card"
            }
        )
    )
)
 
If you have any further questions, please feel free to share them along with a sample output. We're happy to help.
 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

 

Thank you!!

duesouth's avatar
duesouth
Helper I
1 year ago

Many thanks for taking the time to help - much appreciated!