Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filtering a summary table based on a SELECTEDVALUE()

UPDATE: Link to sample pbix   I'm stumped. I have reviewed many third-party sources to no avail. I may very well be missing something obvious. At the end of this post is a section headed Referen...
Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    Anonymous,

     

    Try these measures:

     

    Is Scheduled Sum = 
VAR vSelEmp =
    SELECTEDVALUE ( 'Selected Branch'[Employee ID] )
VAR vTable =
    FILTER (
        'Course Assignment',
        CONTAINSSTRING ( 'Course Assignment'[EmpPath], vSelEmp )
    )
VAR vResult =
    CALCULATE ( SUM ( 'Course Assignment'[Is Scheduled] ), vTable )
RETURN
    vResult

Is Completed Sum = 
VAR vSelEmp =
    SELECTEDVALUE ( 'Selected Branch'[Employee ID] )
VAR vTable =
    FILTER (
        'Course Assignment',
        CONTAINSSTRING ( 'Course Assignment'[EmpPath], vSelEmp )
    )
VAR vResult =
    CALCULATE ( SUM ( 'Course Assignment'[Is Completed] ), vTable )
RETURN
    vResult

Is Late Sum = 
VAR vSelEmp =
    SELECTEDVALUE ( 'Selected Branch'[Employee ID] )
VAR vTable =
    FILTER (
        'Course Assignment',
        CONTAINSSTRING ( 'Course Assignment'[EmpPath], vSelEmp )
    )
VAR vResult =
    CALCULATE ( SUM ( 'Course Assignment'[Is Late] ), vTable )
RETURN
    vResult

     

     

     The table visual doesn't need any filters.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

This is great! Your solution solves my problem and in a manner very different from what I was attempting.
I am sure I can use this approach for handling other issues as well.  I've had to turn to SQL many times because it wasn't clear to me how I could do it with DAX.

Thank you very much.

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    Anonymous,

     

    Glad to hear that solves the problem. That's good design to create a clone of the Employee table (Selected Branch) for use in the slicer. This way you don't disrupt your data model by removing the relationship between Employee and Course Assignment.

     

    Note to the community: This is an example of a well-written post. The author states the issue and desired behavior clearly, lists attempted steps to solve it, provides screenshots of expected results, and provides a link to the pbix.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you, again, for taking the time to help us out. I also appreciate your positive feedback, which hastens me to remind folks about Greg_Deckler 's very useful post : How to Get Your Question Answered Quickly