Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Help with my (multiple functions) formula please?

@Anonymous

Thanks for your response/effort but it doesn't resolve my issue.

As mentioned, I initially provided a rushed description on what I am trying to acheive. I will explain the issue faced in detail (as I should). Please see the below detailed requirement (it overwrites the initial rushed description).

-------------------------------------------------------

I have two tables as follows:

refExpected

refExpected.JPG

 

refSCRIPT_VALIDATION

refSCRIPT_VALIDATION.JPG

 

 

The active relationship between the two tables is refExpected.Session = refSCRIPT_VALIDATION.Session_name. There is an inactive relationship of refExpected.SessionQualification = refSCRIPT_VALIDATION.SessionQualification.

 

There are two filters on the report, which firstly is refSCRIPT_VALIDATION.Session_name and secondly the refSCRIPT_VALIDATION.Qualification. Well actually these filters will be parameters but until I get this issue resolved I will keep them as filters for simplicity.

 

The Session_name filter is mandatory and will always be populated. The Qualification filter is optional.

 

I have card visual showing the value of a measure. The latest variation of my measure is below:

mScriptsExpected = 
IF( ISFILTERED( refSCRIPT_VALIDATION[Qualifications.Qualification] ),
    CALCULATE( 
                       SUM( refExpected[Expected] ),
                       ALLEXCEPT( refSCRIPT_VALIDATION, refSCRIPT_VALIDATION[SessionQualification] )
                       )
   ,
   CALCULATE( SUM( refExpected[Expected] ), ALLEXCEPT( refSCRIPT_VALIDATION, refSCRIPT_VALIDATION[session_name] ) )
  )

 

If only the Session filter (June 2017) is populated then I need the total for refExpected.Expected value for that session - based on the above refExpected table the  value returned will be 4545. If the Qualification filter is populated (GCSE) too then I require the total refExpected.Expected for June 2017 and GCSE - based on the above table will be 1111.

 

I have created an inactive relationship based on two concatenated fields should I need it.

How to I achieve what I am trying to do?  Do I need the inactive relationship I created or can I remove it?

Thanks in advance,

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I have resolved this myself. So to help others if they need I have placed the working formula below.

 

mScriptsExpected =
          IF( ISFILTERED( refSCRIPT_VALIDATION[Qualifications.Qualification] ),
             CALCULATE(
                                SUM( refExpected[Expected] ),
                                FILTER( refExpected, refExpected[Session] = refSCRIPT_VALIDATION[ScriptSession] ),
                                FILTER( refExpected, refExpected[Qualification] = refSCRIPT_VALIDATION[ScriptQualificaton] )
                                )
            ,
CALCULATE( SUM( refExpected[Expected] ), ALLEXCEPT( refSCRIPT_VALIDATION, refSCRIPT_VALIDATION[session_name] ) )
)

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

HI @Anonymous,

 

If you mean you want to filter on multiple columns when you filter on Qualification column, I'd like to suggest you add session to allexcept function to apply filter effect:

 

mScriptsExpected =
IF (
    ISFILTERED ( refSCRIPT_VALIDATION[SessionQualification] ),
    CALCULATE (
        SUM ( refExpected[Expected] ),
        USERELATIONSHIP ( refSCRIPT_VALIDATION[SessionQualification], refExpected[SessionQualification] ),
        ALLEXCEPT (
            refSCRIPT_VALIDATION,
            refSCRIPT_VALIDATION[SessionQualification],
            refSCRIPT_VALIDATION[session_name]
        )
    ),
    CALCULATE (
        SUM ( refExpected[Expected] ),
        ALLEXCEPT ( refSCRIPT_VALIDATION, refSCRIPT_VALIDATION[session_name] )
    )
)

 

If above not help, please share more detailed information to help us clarify your scenario.

How to Get Your Question Answered Quickly

 


Regards,

Xiaoxin Sheng

Anonymous
Not applicable

I have resolved this myself. So to help others if they need I have placed the working formula below.

 

mScriptsExpected =
          IF( ISFILTERED( refSCRIPT_VALIDATION[Qualifications.Qualification] ),
             CALCULATE(
                                SUM( refExpected[Expected] ),
                                FILTER( refExpected, refExpected[Session] = refSCRIPT_VALIDATION[ScriptSession] ),
                                FILTER( refExpected, refExpected[Qualification] = refSCRIPT_VALIDATION[ScriptQualificaton] )
                                )
            ,
CALCULATE( SUM( refExpected[Expected] ), ALLEXCEPT( refSCRIPT_VALIDATION, refSCRIPT_VALIDATION[session_name] ) )
)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors