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

[Sparks]

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 dimensions are connected to a factlesss fact table as shown below. I have also attached the pbix file for reference.

In my visuals, I have two separate slicers that show the list of Area of Studies, one for Type = Major and one for Type = Minor. A sample visual is as shown below:

The first challenge is how do I show the combined list of courses for the Area of Studies selected in both the slicers, like in a Table Visual.

The second challenge, how do i create a measure that shows the distinct count of courses or sum of credits for Area of Studies selected in both the slicers. I have tried couple of ways, for instance, created two measures that count the courses by Area of Study.Type = Major and Type = Minor, and a third measure that adds both. 

The problem I'm seeing is that, when i select a value from one slicer alone, the table visual work fine as well as the measures. But, when I select a value from both slicers, things fail.

Please help.

[v-deddai1-msft · Accepted solution]

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/EWJvRXHiHDhCnz2MYR...

Best Regards,

Dedmon Dai

[v-deddai1-msft · Accepted solution]

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/EWJvRXHiHDhCnz2MYR...

Best Regards,

Dedmon Dai

[Sparks]

@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.

[indrajitha · Accepted solution]

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]

@indrajitha , thanks sir! The updated measure worked!

[Sparks]

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