Query on slicer

[PAVITHRA_R_G]

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 dropped. As per the selection the status should change .. for example if I select 2023 Jan to dec if the patient falls under enrolled from to activation before date then the patient is enrolled. Likewise the status should change but also I need a slicer which have patient status which should change according the date n other filter selections. Can anyone please help me to solve this 

[v-yangliu-msft · Accepted solution]

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

[v-yangliu-msft]

@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]

thank you @v-yangliu-msft 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 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...