Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Summarizing columns from three different date ranges(Please let me know if this the correct approah)

I have created a report that would filter based on three date ranges as below

 

 

 

DateFilterRange = IF(MAX(vw_patientListNew[StatusDate]) in UNION(VALUES(DateRange1[Date]),VALUES(DateRange2[Date]),VALUES(DateRange3[Date])),1)

 

 

 

Now i want to summarize it like the table below

 TimeFrameDateRangeCol1TimeFrameDateRangeCol2TimeFrameDateRangeCol3
Interventional TX236512
Interventional Non TX13419

 

 

 

TimeFrameDateRangeCol1 = 
IF(MAX(vw_patientListNew[StatusDate]) in VALUES(DateRange1[Date]),1)
TimeFrameDateRangeCol2 = 
IF(MAX(vw_patientListNew[StatusDate]) in VALUES(DateRang2[Date]),1)
TimeFrameDateRangeCol3 = 
IF(MAX(vw_patientListNew[StatusDate]) in VALUES(DateRange3[Date]),1)

 

 

TimeFrameDateRangeCol1, TimeFrameDateRangeCol2, TimeFrameDateRangeCol3  are calculation column and should return 1 when the statusdate intersect the respective filter. As you can see i am not able to achieve with the Daxcode.

 

Once i get the values for the each collumn TimeFrameDateRangeCol1, TimeFrameDateRangeCol2, TimeFrameDateRangeCol3 i can summarize TimeFrameDateRangeCol1, TimeFrameDateRangeCol2, TimeFrameDateRangeCol3 to create a summary table.

 

Is there another solution to this? 

 

Need Help

12 Replies

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      6 years ago

      Anonymous not sure how tables are connected like slicers are from disconnected tables, you have to add your calculation as measures, not columns

       

      I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

      ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        These are the tables from where slicer data is coming. 

        I have added the calculation measure and i cannot summarize the data. I can only summarize the data with a column.

        My goal here is to create a summarized table as shown in my post.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       

      Spoiler
      amitchandak  Yes the slicers are coming from three different date tables, exactly as you mention in the article link.

       As you can see above the slicer data is coming from three different tables.

       

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi Anonymous,

     

    Are you able to provide some sample data and what the expected outcome would be. One thing to note is that calcuated columns do not evaluate in the context of any filters or slicers applied to the report, they are calcluated when the model is processed, the values are then locked in place. Measures on the other hand are evaluated when the model is queried by the report.

     

    Hope that makes sense