Forum Discussion

PAVITHRA_R_G's avatar
PAVITHRA_R_G
Regular Visitor
2 years ago
Solved

Query on slicer

Hello , I have a query. Im having a table with patient id, enrolled from date, activated from date and dropped from date . I want a column which calculate the patient status as enrolled, active and ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  PAVITHRA_R_G ,

     

    Here are the steps you can follow：

    1. Create calculated table.

    Date =
CALENDAR(
    DATE(2023,1,1),
    DATE(2024,12,31))

    2. Enter data – create a group table.

    3. Create measure.

    Measure =
var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date])
var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date])
var _column=
SELECTCOLUMNS(FILTER(ALL('Date'),'Date'[Date]>=_mindate&&'Date'[Date]<=_maxdate),"test",[Date])
return
SWITCH(
    TRUE(),
MAX('Table'[Activated from]) in _column && MAX('Table'[Dropped from]) in _column,"dropped",
MAX('Table'[Enrolled from]) in _column && MAX('Table'[Activated from]) in _column,"active",
MAX('Table'[Enrolled from]) = MAX('Table'[Activated from]) ,"active")
    Measure 2 =
COUNTX(
    FILTER(ALL('Table'),
    [Measure]=MAX('Group_Table'[Group])),[Person ID])

    4. Result:

     

     

    Best Regards,

    Liu Yang

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:
Hi  PAVITHRA_R_G ,

I created some data:

Here are the steps you can follow：

Does the statement " if I select 2023 Jan to dec if the patient falls under enrolled from to activation before date then the patient is enrolled " mean that the enrolled date and activated date are before the minimum date selected by the slicer

1. Create calculated table.

Table 2 =
CALENDAR(
    DATE(2023,1,1),DATE(2025,12,31))

2. Create measure.

Measure = 
var _mindate=MINX(ALLSELECTED('Table 2'),'Table 2'[Date])
var _minmonthdate=DATE(YEAR(_mindate),MONTH(_mindate),1)
RETURN
IF(
  MAX('Table'[enrolled from date])<_minmonthdate&&MAX('Table'[activated from date])<_minmonthdate,
    "enrolled")

3. Result:

As far as I know, only calculated columns can be placed on top of the slicer, you need to use Enter data to create a table containing patient status, using [patient status] as the slicer

1. Then create a measure to customize the rules for each status, such as the following:

Test = 
var _mindate=MINX(ALLSELECTED('Table 2'),'Table 2'[Date])
var _minmonthdate=DATE(YEAR(_mindate),MONTH(_mindate),1)
var _status=
SWITCH(
    TRUE(),
    MAX('Table'[enrolled from date])<_minmonthdate&&MAX('Table'[activated from date])<_minmonthdate&&MAX('Table'[dropped from date])<_minmonthdate,"dropped",
    MAX('Table'[enrolled from date])<_minmonthdate&&MAX('Table'[activated from date])<_minmonthdate,
    "enrolled")
return
_status
Flag =
var _test=
SELECTCOLUMNS(
    'Table',"test",[Test])
RETURN
IF(
    MAX('status table'[patient status]) in _test,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result：

If the above results do not meet your expectations, can you express the expected results in the form of a picture, we can help you better.

 

Best Regards,

Liu Yang

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

  • PAVITHRA_R_G's avatar
    PAVITHRA_R_G
    Regular Visitor
    2 years ago

    thank you Anonymous for your response. This is much useful but my requirement need so more additions. I have send you the full requirement details in that image which I'm attaching with this. Thanks in advance