Create Slicer for Measure ,

[Chanleakna123]

Hi , 

good day. 

I have built a report which to track how many Outlets are Active or InActive , All are Tracking based on duration of 3 , 6 & 12 Months.

I have created the formula for 3 months , 6 months and 12 months with this Measure : 

*3 Months Tracking = CALCULATE(SUM('Unproductive Outlets'[Count]),DATESINPERIOD(Canlendar[Date],ENDOFMONTH('Unproductive Outlets'[Date]),-3,MONTH))

And i do the same for 6 Months & 12 Months.

And then i have created table for  Slicer using below Measure : 

**Type = SELECTEDVALUE('Duration Type'[Duration],[Rolling 12])

**Duration 3,6,12 = SWITCH(TRUE(),

[Rolling 12 Last 12 selection type]="Rolling 3",[Rolling 3],

[Rolling 12 Last 12 selection type]="Rolling 6",[Rolling 6],

[Rolling 12 Last 12 selection type]="Rolling 12",[Rolling 12],

BLANK())

Now i am on this stage , my purpose is to have a slicer of Active and In-Active , so i can click on the Slicer whether it is Active or In-Active  to see how many outlets are Productive Or Unproductive , But I am Stuck . 

I am using this Measure : 

Active or In-Active = IF('Duration Type'[Duration 3,6,12]>=1,"Active",

IF('Duration Type'[Duration 3,6,12]<1,"Inactive"))

Actually i have a slicer of 3 months , 6 months , 12 months duration alr , but i am stuck over the Slicer of Active or In- Active . 
Because mypurpose to Click on the duration and click on Active or In-Active.

Since i have 45K Outlets , i would love to have a slicer of Active Or In-Active , that would be easier for me to track each customers. 

[PaulDBrown]

@Chanleakna123 

Apologies upfront since I’m typing this from a tablet (so no PBI Desktop to test measures etc...).

To be able to see either Active Or Inactive outlets, you can create a new table using the “Enter data” (to generate a table) with the values Active and Inactive in two rows of the table. Let’s call this table Activity Status.

make sure the table has no relationships with other tables in your model.

then create a measure which goes along the lines of:

Activity Status = IF(SELECTEDVALUE(Status Activity[column]) = “Active”, [your active measure], [your inactive measure])

Use this measure in you visual (table or whatever), and create a new slicer with the column from Activity Status table.

[v-xicai]

Hi @Chanleakna123 ,

The slicer supports calculated column instead of measure, so you might create [Active or In-Active] using column, while you may need to click refresh button once the data changed.

There is another solution to filter Active or Inactive, you can create measure Filter1 like DAX below first of all, then put the Filter1 in the Visual Level Filter of Matrix visual which displays [Customer Name] and other fields, setting Filter1 as "is not blank".

Filter1=IF ([Active or In-Active]="Active", 1, BLANK())

Best Regards,

Amy

Community Support Team _ Amy

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

[JQuon]

Would you benefit using a calculated column instead of a measure and using that data field as the slicer?

[Chanleakna123]

Hi , can u show me how ? I have used calculated column , but it turns not eights , since all the data of 3 months , 6 & 12 months are already combined with measure.

[JQuon]

Actually, after reviewing your question and the design behind the logic, I understand where the complication is.  Let me digest this a little more and see if I can come up with a solution.

[Chanleakna123]

@JQuon , hi  do you have any update in this regards ? 
looking forward your response 🙂 . 

Thanks with Regards, 
Chanleakna Hang 

[JQuon]

@Chanleakna123 , are you able to share the PBIX file?  I think the only way to do this is through calculated columns.  It may not be ideal, but you may not to do calculated columns on an aggregate table to achieve your goal.

[Chanleakna123]

hi @v-xicai  it doesn't shown the preference result . 

HI @JQuon  i have shared through the message of the BI File. Can you please review it ? 

thx 

[amitchandak]

If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Chanleakna123]

hi @JQuon , i haven't received any feedbacks on this , can you please help 

[Ashish_Mathur]

Hi,

I would like ot help.  Can you take a very simple example and show me your expected result.