Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Totaling Multiple Filtered Measures

Hello,

 

I am putting together a type of weighted caseload for my staff members. It is kind of complex and i am wondering if anyone has any ideas or better ways to go about this or even a dax command that may work. Essentially each staff member sees a certain amount of poeple daily. Each person recieves different services and so on that derive from different SQL tables that i have. I have created several individual table visuals getting the correct total data for each category (Total of each visual is filtered in the visual) with measures. However is there a way to add the filtered Total data in a measure for each category up as a whole to find their total score for all the people they see if that makes sense? This is example data below by the way as i cannot list the actual data.

 

Here is an example. I have a visual filtered to look back one year, then i have applied this measure to it to count each row of data and multiply it by 20 to get the score for each row.

 

Inpatient 1 Year Score = COUNT(Admissions[Case#])*20
 
Next i have another visual total adding all of their 5 year stays together similar to the one above.  
 
Inpatient 5 Year Score = COUNT(Admissions[Case#])*5
 
Hence for every patient in the last year with a admission would recieve 20 points for each admission and every patient admission in the last 5 years would receive 5 points per admission
 
 
example

 

 
Would it be easier to create a scoring table and add it in? This may be something not doable in Powerbi as well i am not sure. Her total score then for admissions would be 30 and i would add that to a bunch of total visuals like this one. Maybe i need to make these column dax statements too instead of measures? Any help would be greatly appreciated!
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Please check the measure below.

    Measure =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[case] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[date] >= EDATE ( TODAY (), -12 ) )
    ) * 20
        + CALCULATE (
            DISTINCTCOUNT ( 'Table'[case] ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[date] >= EDATE ( TODAY (), -60 ) )
        ) * 5

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

     

    Please check the measure below.

    Measure =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[case] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[date] >= EDATE ( TODAY (), -12 ) )
    ) * 20
        + CALCULATE (
            DISTINCTCOUNT ( 'Table'[case] ),
            FILTER ( ALLSELECTED ( 'Table' ), 'Table'[date] >= EDATE ( TODAY (), -60 ) )
        ) * 5

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank you so much!

    • Anonymous's avatar
      Anonymous
      Not applicable

      I have one more question  as well using the below dax statement. I want to add another total to this but i want to filter by a specific value name in a column instead of date. What would that entail?

       

      Measure =
      CALCULATE (
          DISTINCTCOUNT ( 'Table'[case] ),
          FILTER ( ALLSELECTED ( 'Table' ), 'Table'[date] >= EDATE ( TODAY (), -12 ) )
      ) * 20
          + CALCULATE (
              DISTINCTCOUNT ( 'Table'[case] ),
              FILTER ( ALLSELECTED ( 'Table' ), 'Table'[date] >= EDATE ( TODAY (), -60 ) )
          ) * 5

       

    • Anonymous's avatar
      Anonymous
      Not applicable

      Is there anyway to get this to add up correctly when you take the individual filter off too? I am finding that it adds quite a bit to the score number when taking the individual filter off by person and looking at everyone as a whole.