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[Da...
Need Help
richbenmintz's avatar
richbenmintz
Icon for Resident Rockstar rankResident Rockstar
6 years ago

Hi Anonymous,

 

If you can provide the .pbix file I am pretty sure that parry2k , amitchandak  or myself can help you with the measure you are looking for.

 

Thanks,

 

Richard 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

The site doesn't let me upload file.

 

Data:

StatusDateInterventionType
7/21/1989 0:00Interventional TX
1/8/1990 0:00Interventional TX
4/30/1990 0:00Interventional TX
11/3/2006 0:00Interventional Non TX
11/6/2006 0:00Interventional TX
11/8/2006 0:00Interventional Non TX
11/9/2006 0:00Interventional TX
11/13/2006 0:00Interventional TX
11/14/2006 0:00Interventional Non TX
11/14/2006 0:00Interventional TX
11/15/2006 0:00Interventional Non TX
11/16/2006 0:00Interventional TX
11/17/2006 0:00Interventional Non TX
11/20/2006 0:00Interventional TX
11/22/2006 0:00Interventional Non TX
11/22/2006 0:00Interventional TX
11/27/2006 0:00Interventional TX
11/28/2006 0:00Interventional Non TX
11/28/2006 0:00Interventional TX
11/30/2006 0:00Interventional Non TX
11/30/2006 0:00Interventional TX
12/1/2006 0:00Interventional Non TX
12/1/2006 0:00Interventional TX
12/4/2006 0:00Interventional TX
12/5/2006 0:00Interventional TX
12/6/2006 0:00Interventional TX
12/7/2006 0:00Interventional TX
12/11/2006 0:00Interventional Non TX
12/11/2006 0:00Interventional TX
12/12/2006 0:00Interventional Non TX
12/12/2006 0:00Interventional TX
12/13/2006 0:00Interventional Non TX
12/14/2006 0:00Interventional TX
12/15/2006 0:00Interventional Non TX
12/18/2006 0:00Interventional Non TX
12/19/2006 0:00Interventional Non TX
12/19/2006 0:00Interventional TX
12/20/2006 0:00Interventional Non TX
12/20/2006 0:00Interventional TX
12/21/2006 0:00Interventional Non TX
12/21/2006 0:00Interventional TX
12/22/2006 0:00Interventional Non TX
12/27/2006 0:00Interventional TX
12/28/2006 0:00Interventional TX
1/2/2007 0:00Interventional Non TX
1/4/2007 0:00Interventional TX
1/5/2007 0:00Interventional Non TX
2/24/2020 0:00Interventional Non TX
2/24/2020 0:00Interventional TX
2/25/2020 0:00Interventional Non TX
2/25/2020 0:00Interventional TX
2/26/2020 0:00Interventional Non TX
2/26/2020 0:00Interventional TX
2/27/2020 0:00Interventional Non TX
2/27/2020 0:00Interventional TX
2/28/2020 0:00Interventional Non TX
2/28/2020 0:00Interventional TX
3/1/2020 0:00Interventional Non TX

 

Desired Results :

 Slicer1(1/1/1989 - 7/26/1990)Slicer2(11/3/2006 - 1/5/2007)Slicer3(2/24/2020-3/1/2020)
Interventional TX3245
Interventional Non TX0206

 

I have three slicers: 

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

    Hi Anonymous 

     

    Please have a look at the .pbix file created.

     

    sample.pbix 

     

    I contains 4 measures:

     

    Slicer 1 Value = CALCULATE(COUNTROWS('Table'), 'Table'[StatusDate] in VALUES('slicer 1'[Date]))
Slicer 2 Value = CALCULATE(COUNTROWS('Table'), 'Table'[StatusDate] in VALUES('slicer 2'[Date]))
Slicer 3 Value = CALCULATE(COUNTROWS('Table'), 'Table'[StatusDate] in VALUES('slicer 3'[Date]))
Total Slicer Value Selection = [Slicer 1 Value] + [Slicer 2 Value] + [Slicer 3 Value]

     

    which evaluate to the values you are looking for

     

     

    Hope this helps

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      richbenmintz Yes this the result I was expecting but when I click on the slicer 2 value and "Interventional Non TX" which has 7 counts should only display 7 but display the entire row count 13. Is there a way to display only 7 in detail report? should I use a matrix ? please suggest

       

       

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

        Hi Anonymous,

         

        Not really sure what you mean selecting the value in the table created it does not filter the table, what detail visual are you referring to