topic Re: Query on slicer in Developer

Query on slicer

PAVITHRA_R_G — Sat, 03 Aug 2024 19:22:17 GMT

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

Re: Query on slicer

lbendlin — Sun, 04 Aug 2024 15:57:47 GMT

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-Forum/ba-p/963216

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/1447523

Re: Query on slicer

Anonymous — Mon, 05 Aug 2024 02:35:19 GMT

@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

Re: Query on slicer

PAVITHRA_R_G — Wed, 07 Aug 2024 07:07:46 GMT

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

Re: Query on slicer

Anonymous — Fri, 09 Aug 2024 05:06:12 GMT

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