Forum Discussion

Sparks's avatar
Sparks
Helper I
6 years ago
Solved

Creating Measures that take context from multiple slicers based on same dimension

Lets say, I have a Dimension called 'Area of Study' that contains a list of Area of Studies of Type Major and Minor. Each of these Area of Studies have a list of Courses (Dimension). Both of these di...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Sparks ,

     

    Would you please follow the following steps?

     

    1.Create two calculated table for the Major slicer and the Minor slicer:

     

     

    Majorslicer = CALCULATETABLE(VALUES('Area of Study'[Area of Study Name]), FILTER('Area of Study','Area of Study'[Type] = "Major"))



Minorslicer = CALCULATETABLE(VALUES('Area of Study'[Area of Study Name]), FILTER('Area of Study','Area of Study'[Type] = "Minor"))

     

    2.Create a measure for filter the table visual:

     

     

    Measure =

VAR a =

CALCULATETABLE (

VALUES ( CourseList[CourseSK] ),

FILTER (

ALL(CourseList),

RELATED ( 'Area of Study'[Area of Study Name] )

= SELECTEDVALUE ( Majorslicer[Area of Study Name] )

)

)

VAR b =

CALCULATETABLE (

VALUES ( CourseList[CourseSK] ),

FILTER (

ALL(CourseList),

RELATED ( 'Area of Study'[Area of Study Name] )

= SELECTEDVALUE ( Minorslicer[Area of Study Name] )

)

)

RETURN

IF ( MAX ( Courses[CourseSK] ) IN a || MAX ( Courses[CourseSK] ) IN b, 1, 0 )

     

     

    And put the measure in table visual filter:

     

     

     

    For the dax code modification of your other measures, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EWJvRXHiHDhCnz2MYRgm05gBTDy7fl7pHG2muAzQtShzaA?e=LV3eS2

     

    Best Regards,

    Dedmon Dai

  • Anonymous's avatar
    Anonymous
    6 years ago

    You can modify the measure as below:

    FlagShowCourse =
VAR a =
    CALCULATETABLE (
        VALUES ( CourseList[CourseSK] ),
        FILTER (
            ALL ( CourseList ),
            RELATED ( 'Area of Study'[Area of Study Name] )
                IN VALUES ( Majorslicer[Area of Study Name] )
        )
    )
VAR b =
    CALCULATETABLE (
        VALUES ( CourseList[CourseSK] ),
        FILTER (
            ALL ( CourseList ),
            RELATED ( 'Area of Study'[Area of Study Name] )
                IN VALUES ( Minorslicer[Area of Study Name] )
        )
    )
VAR SelectedMajorCount =
    IF (
        NOT ISFILTERED ( Majorslicer[Area of Study Name] ),
        0,
        IF (
            CALCULATE ( COUNTROWS ( Majorslicer ) )
                == CALCULATE ( COUNTROWS ( Majorslicer ), ALL ( Majorslicer ) ),
            1,
            CALCULATE ( COUNTROWS ( Majorslicer ), ALL ( Majorslicer ) )
                - CALCULATE ( COUNTROWS ( Majorslicer ) )
        )
    )
VAR SelectedMinorCount =
    IF (
        NOT ISFILTERED ( Minorslicer[Area of Study Name] ),
        0,
        IF (
            CALCULATE ( COUNTROWS ( Minorslicer ) )
                == CALCULATE ( COUNTROWS ( Minorslicer ), ALL ( Minorslicer ) ),
            1,
            CALCULATE ( COUNTROWS ( Minorslicer ), ALL ( Minorslicer ) )
                - CALCULATE ( COUNTROWS ( Minorslicer ) )
        )
    )
RETURN
    IF (
        (
            MAX ( Courses[CourseSK] ) IN a
                && SelectedMajorCount > 0
        )
            || (
                MAX ( Courses[CourseSK] )
                    IN b
                    && SelectedMinorCount > 0
            ),
        1,
        0
    )

    See if this helps!!!. Thank you:)

v-deddai1-msft's avatar
v-deddai1-msft
Community Support
6 years ago

Hi Sparks ,

 

Would you please follow the following steps?

 

1.Create two calculated table for the Major slicer and the Minor slicer:

 

 

Majorslicer = CALCULATETABLE(VALUES('Area of Study'[Area of Study Name]), FILTER('Area of Study','Area of Study'[Type] = "Major"))



Minorslicer = CALCULATETABLE(VALUES('Area of Study'[Area of Study Name]), FILTER('Area of Study','Area of Study'[Type] = "Minor"))

 

2.Create a measure for filter the table visual:

 

 

Measure =

VAR a =

CALCULATETABLE (

VALUES ( CourseList[CourseSK] ),

FILTER (

ALL(CourseList),

RELATED ( 'Area of Study'[Area of Study Name] )

= SELECTEDVALUE ( Majorslicer[Area of Study Name] )

)

)

VAR b =

CALCULATETABLE (

VALUES ( CourseList[CourseSK] ),

FILTER (

ALL(CourseList),

RELATED ( 'Area of Study'[Area of Study Name] )

= SELECTEDVALUE ( Minorslicer[Area of Study Name] )

)

)

RETURN

IF ( MAX ( Courses[CourseSK] ) IN a || MAX ( Courses[CourseSK] ) IN b, 1, 0 )

 

 

And put the measure in table visual filter:

 

 

 

For the dax code modification of your other measures, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EWJvRXHiHDhCnz2MYRgm05gBTDy7fl7pHG2muAzQtShzaA?e=LV3eS2

 

Best Regards,

Dedmon Dai

  • Sparks's avatar
    Sparks
    Helper I
    6 years ago

    Thanks a ton for the solution, Sir! This helps a lot!

  • Sparks's avatar
    Sparks
    Helper I
    6 years ago

    v-deddai1-msft sir, there is one problem here. The SELECTEDVALUE() would work when only one value is selected. If I replace the SELECTEDVALUE() with "IN VALUES(Minorslicer[Area of Study Name])", then I end up another problem, that is if I don't select any Area of Study Name, all values are considered and all courses of all area of studies are listed.

    I tried the following below, but the issue here would be that if I have only one Area of Study in my system or I select all the values, then the count difference "SelectedMajorCount" or "SelectMinorCount" will be 0 and no courses will be li

     

     

    FlagShowCourse = 
VAR a =
    CALCULATETABLE (
        VALUES ( CourseList[CourseSK] ),
        FILTER (
            ALL(CourseList),
            RELATED ( 'Area of Study'[Area of Study Name] )
                IN VALUES( Majorslicer[Area of Study Name] )
        )
    )
VAR b =
    CALCULATETABLE (
        VALUES ( CourseList[CourseSK] ),
        FILTER (
            ALL(CourseList),
            RELATED ( 'Area of Study'[Area of Study Name] )
               IN VALUES ( Minorslicer[Area of Study Name] )
        )
    )
VAR SelectedMajorCount = CALCULATE(COUNTROWS(Majorslicer),All(Majorslicer))-CALCULATE(COUNTROWS(Majorslicer))
VAR SelectedMinorCount = CALCULATE(COUNTROWS(Minorslicer),All(Minorslicer))-CALCULATE(COUNTROWS(Minorslicer))
RETURN
    IF ( (MAX ( Courses[CourseSK] ) IN a && SelectedMajorCount >0) || (MAX ( Courses[CourseSK] ) IN b && SelectedMinorCount >0 ), 1, 0 )

     

     

    Any Ideas? Please help.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      You can modify the measure as below:

      FlagShowCourse =
VAR a =
    CALCULATETABLE (
        VALUES ( CourseList[CourseSK] ),
        FILTER (
            ALL ( CourseList ),
            RELATED ( 'Area of Study'[Area of Study Name] )
                IN VALUES ( Majorslicer[Area of Study Name] )
        )
    )
VAR b =
    CALCULATETABLE (
        VALUES ( CourseList[CourseSK] ),
        FILTER (
            ALL ( CourseList ),
            RELATED ( 'Area of Study'[Area of Study Name] )
                IN VALUES ( Minorslicer[Area of Study Name] )
        )
    )
VAR SelectedMajorCount =
    IF (
        NOT ISFILTERED ( Majorslicer[Area of Study Name] ),
        0,
        IF (
            CALCULATE ( COUNTROWS ( Majorslicer ) )
                == CALCULATE ( COUNTROWS ( Majorslicer ), ALL ( Majorslicer ) ),
            1,
            CALCULATE ( COUNTROWS ( Majorslicer ), ALL ( Majorslicer ) )
                - CALCULATE ( COUNTROWS ( Majorslicer ) )
        )
    )
VAR SelectedMinorCount =
    IF (
        NOT ISFILTERED ( Minorslicer[Area of Study Name] ),
        0,
        IF (
            CALCULATE ( COUNTROWS ( Minorslicer ) )
                == CALCULATE ( COUNTROWS ( Minorslicer ), ALL ( Minorslicer ) ),
            1,
            CALCULATE ( COUNTROWS ( Minorslicer ), ALL ( Minorslicer ) )
                - CALCULATE ( COUNTROWS ( Minorslicer ) )
        )
    )
RETURN
    IF (
        (
            MAX ( Courses[CourseSK] ) IN a
                && SelectedMajorCount > 0
        )
            || (
                MAX ( Courses[CourseSK] )
                    IN b
                    && SelectedMinorCount > 0
            ),
        1,
        0
    )

      See if this helps!!!. Thank you:)

      • Sparks's avatar
        Sparks
        Helper I
        6 years ago

        Anonymous , thanks sir! The updated measure worked!