Measure created based on multiple records - group by

[CB]

Hello,

This is a follow up to this earlier soution:
https://community.powerbi.com/t5/Desktop/dax-group-by-measure/m-p/214154/highlight/true#M94717

The following is my question. 

=================================

I have a table of patients and clinical results (Blood pressure):

patientid      date      value,   program status (indicates if enrolled in a specific health program)
1                 1/1/18    87        inactive
1                 2/1/18    80        active

I need to have a flag on each PATIENTT to indicate if the patient is active at any point during a daterange I select from a slicer. If there is even one record for the patient that has "program status" =  active during the slicer range, all rows of the patient should be flagged as true. If none of the records are showing "active" for the slicer range, all rows should be false. 

If I choose 1/1/18 - 1/15/18, I need to see:

patientid      date      value,   program status  ActiveInDateRange (this indicates the patient is not active during the time selected regardless if there are some rows of the patient showing active )
1                 1/1/18    87        inactive                    false
1                 2/1/18    80        active                       false

If I choose 1/1/18 - 3/1/18 I should see:
patientid      date      value,   program status  ActiveInDateRange (this indicates the patient IS  active during the time selected regardless if threre are specific rows of the patient showing inactive )
1                 1/1/18    87        inactive                    true
1                 2/1/18    80        active                       true

Essentially i'm trying to say "find all rows of the patient (group by patient) where program status = true and if count > 0 then the new measure should be "true", otherwise "false". 

Can you help me create this measure? 

[CB]

Thank you. Can you explain why we are using ALLEXCEPT? I have not yet tried this on the particular data I originally asked it about but will work on it. 

I tried this in a similar measure to find the average of measure B but only average the records that are not 0 in measure B and measure C:

//Subract the average of " A1c After" (measure B) from "A1c Before" (Measure C)but I don't want to include any records that have 0 in measure B or measure C so that those don't get included in the average. 

A1C Change Before/After = 
CALCULATE(
AVERAGEX('Lab', 'Lab'[A1C AVG After] ) - AVERAGEX('Lab', 'Lab'[A1C AVG Before] )
,FILTER(allexcept('Lab', 'Lab'[Patient ID]),'Lab'[A1C AVG After] <> 0 && 'Lab'[A1C AVG Before] <> 0)
)

However, this returns 0 for all even though there are many that have both the before and after which should show a true change.  

I also tried this but it also returned 0 only:

A1C Change Before/After = 
CALCULATE(
AVERAGEX(FILTER(allexcept('Lab', 'Lab'[Patient ID]),'Lab'[A1C AVG After] <> 0 && 'Lab'[A1C AVG Before] <> 0),  'Lab'[A1C AVG After]  - 'Lab'[A1C AVG Before] )

)

[v-juanli-msft]

Hi @CB

First you need to calculate the average for measureB and measureC which should consider the condition.

Then substract the average of measureB and the average of measureC.

You can use the following formula instead.

Measure =
VAR AVERGEB =
    AVERAGEX (
        FILTER ( ALLEXCEPT ( Table1, Table1[patientid] ), [MeasureB] <> 0 ),
        [MeasureB]
    )
VAR AVERGEC =
    AVERAGEX (
        FILTER ( ALLEXCEPT ( Table1, Table1[patientid] ), [MeasureC] <> 0 ),
        [MeasureC]
    )
RETURN
    AVERGEB - AVERGEC

Hope mu answer will help you. 

Best Regards

Maggie

[CB]

Hello,

You are right, I should keep the different questions separate. 

The ALLEXCEPT usage does not take into account any of the slicers that are on the report page. So though it might correctly be showing a measure, it's doing so for ALL patients even though the report page is showing only a subset of the patients due to the slicer. How can we do this grouping and still take into account the slicers that applied? 

[CB · Accepted solution]

In the end I accomplished the grouping by patient by using the "summarize" with filter clause. I had to create multiple metrics: (metrics vs. calculated columns so they reflect the slicer values selected). 

1) FlagAfter - this indicated if the patient had a "After" value. 

2FlagAfter = 
if (
      calculate (
     count('Lab'[Patient ID]),
     FILTER('Lab',
          'Lab'[1AfterEnrollment] <> BLANK()
           )
 ) > 0, "hasAfter", "missingAfter")

2) FlagBefore - this indicated if the patient had a "Before" value

2FlagBefore = 
if (
      calculate (
     count('Lab'[Patient ID]),
     FILTER('Lab',
          'Lab'[1BeforeEnrollment] <> BLANK()
           )
 ) > 0, "hasBefore", "missingBefore")

3) FlagAfterBefore - this provided the count of patients who had both a  before and after. 

3FlagAfterBefore = 
var countpatient = 
COUNTX(
      SUMMARIZE(//This groups by patientId and finds all the rows for that patient and checks if across all there is a hasAfter and hasBefore
               filter('lab', 'Lab'[2FlagAfter]= "hasAfter" &&'Lab'[2FlagBefore] = "hasBefore"),
               Lab[Patient ID],
               "count",'Lab'[Patient ID]               ),
           [count]
   )
   
Return IF(countpatient <> BLANK(), countpatient, 0)

4) ChangeBeforeAfter - this is the "after minus before" across all those that have both (average calculation) 

4 A1C Change Before/After = 
var change = 
CALCULATE(//calculate the average (total after divide by rows that have both bef and aft) minus (total before divided by rows that have both bef and aft)
  SUMX(
        FILTER('lab','Lab'[3FlagAfterBefore]<> 0),
       'Lab'[A1C AVG After])/ 'Lab'[3FlagAfterBefore] -
       
        SUMX(
        FILTER('lab','Lab'[3FlagAfterBefore]<> 0),
       'Lab'[A1C AVG Before])/ 'Lab'[3FlagAfterBefore] 
)

Thank you for your suggestions. It helped me find the right lead. 

[v-juanli-msft]

Hi @CB

For the formula above using ALLEXCEPT, it means to count rows of a column for each Patient ID, this is to say count for the same Patient ID is same and for different Patient ID is not the same.

The formula above I provided is tested for the original problem you post,but not consider beyond your original information, if you have any other problem, since i can't catch all your situation in your new question, please open another ticket to get better answer.

Anyway, I will test for your problem in the last reply, I will come back soon.

Best Regards

Maggie

[v-juanli-msft]

Hi @CB

1.create a new table

date = CALENDARAUTO()

don’t build a relationship between this table and the original table

2.create measures

flag = IF(MAX([date])>=MIN('date'[Date])&&MAX([date])<=MAX('date'[Date]),1,0)

count =
CALCULATE (
    COUNT ( Table1[program status] ),
    FILTER (
        ALLEXCEPT ( Table1, Table1[patientid] ),
        [program status] = "active"
            && [flag] = 1
    )
)

status = IF([count]>0,"True","Flase")

Best Regards

Maggie