Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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:)
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 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:)
Thanks a ton for the solution, Sir! This helps a lot!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |