Need Help with Dax

Hi Krushnab85 ,

Since you cannot use inactive relationships (which would have been a typical solution using USERELATIONSHIP), you need an approach that dynamically counts patients based on different date fields while keeping your Calendar Table as the main filter context.

Solution: Using Measures Without Inactive Relationships
You can create separate measures for each stage and count patients based on the respective date column.

1. Measure for Enrollments Count
This measure counts patients where the Enrollment Date falls in the selected month.

No_of_Enrollments =
CALCULATE(
COUNT(Patient_Fact[Patient ID]),
TREATAS( VALUES(Calendar[Cal_Date]), Patient_Fact[Enrollment Date])
)

2. Measure for Admissions Count
This measure counts patients where the Admission Date falls in the selected month.No_of_Admissions =

CALCULATE(
COUNT(Patient_Fact[Patient ID]),
TREATAS( VALUES(Calendar[Cal_Date]), Patient_Fact[Admission Date])
)

3. Measure for Billings Count
This measure counts patients where the Billing Date falls in the selected month.

No_of_Billings =
CALCULATE(
COUNT(Patient_Fact[Patient ID]),
TREATAS( VALUES(Calendar[Cal_Date]), Patient_Fact[Billing date])
)

4. Measure for Infusion Count
This measure counts patients where the Infusion Date falls in the selected month.

No_of_Infusions =
CALCULATE(
COUNT(Patient_Fact[Patient ID]),
TREATAS( VALUES(Calendar[Cal_Date]), Patient_Fact[Infusion date])
)

Final Steps:
1. Use a Column Chart

X-axis → Calendar[Cal_Month]
Y-axis → Add all four measures:
No_of_Enrollments
No_of_Admissions
No_of_Billings
No_of_Infusions

2. Set Data Colors

Different colors for each measure to distinguish the stages.

Please mark this post as solution if it helps you. Appreciate Kudos.

what about the next few months? Do you also need to show enrollments for Feb Mar and Apr?

Hi  Krushnab85

1. Prepare Your Data

• Import Data:
  • Load your Calendar Table and Patient Fact Table into Power BI.
  • Ensure that date columns (like Cal_Date, Enrollment Date, Admission Date, etc.) are set to the correct data type (Date).

    ---

    2. Set Up the Data Model

    • Create Relationships:
      • In Model View, create an active relationship between Calendar Table[Cal_Date] and Patient Fact Table[Enrollment Date].

        

        3. Create DAX Measures Using TREATAS

        • Enrollment Count = COUNTROWS('Patient_Fact')
        • Admission Count =  CALCULATE(
                                             COUNTROWS('Patient_Fact'),
          TREATAS(VALUES('Calendar_Table'[Cal_Date]), 'Patient_Fact'[Admission_Date]))
        • Billing Count = CALCULATE(
                                     COUNTROWS('Patient_Fact'),
          TREATAS(VALUES('Calendar_Table'[Cal_Date]), 'Patient_Fact'[Billing_Date]))
        • Infusion Count = CALCULATE(
                                        COUNTROWS('Patient_Fact'),
          TREATAS(VALUES('Calendar_Table'[Cal_Date]), 'Patient_Fact'[Infusion_Date]))

           

          

        
        
        

        Best regards,
        Ray Minds
        http://www.rayminds.com
        https://www.linkedin.com/company/rayminds/

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