Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

 

refSCRIPT_VALIDATION

 

 

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,

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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