Forum Discussion
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 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.
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
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:)
5 Replies
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
Thanks a ton for the solution, Sir! This helps a lot!
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.
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:)
