Forum Discussion

Krushnab85's avatar
Krushnab85
Helper I
1 year ago
Solved

Need Help with Dax

Hi, I have calendar Dimension Table and i have patient fact table having cardinality one to many from calendar to Patient Table (Cal_Date >>Enrollment Date).  Now i want to calculate the counts of pa...
Need Help
Show and Tell
Tips and Tricks
  • FarhanJeelani's avatar
    FarhanJeelani
    1 year ago

    Hi Krushnab85 ,

    To ensure that the calendar filters the correct stage date dynamically, modify your measures using ALL to ignore the active relationship and then apply the correct date filter using TREATAS.


    Enrollments Count (Respects Active Relationship)

    No_of_Enrollments = COUNT(Patient_Fact[Patient ID])

    (This works as expected since Cal_Date >> Enrollment Date is active)

     

    Admissions Count

    No_of_Admissions =
CALCULATE(
COUNT(Patient_Fact[Patient ID]),
ALL(Patient_Fact), -- Removes any filter from Enrollment Date
TREATAS( VALUES(Calendar[Cal_Date]), Patient_Fact[Admission Date])
)


    Billings Count

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


    Infusions Count

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

     

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

Ray_Minds's avatar
Ray_Minds
Solution Supplier
1 year ago

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.