Forum Discussion

Chanleakna123's avatar
Chanleakna123
Post Prodigy
6 years ago

Create Slicer for Measure ,

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. 

 

 

Need Help

11 Replies

  • JQuon's avatar
    JQuon
    Helper I
    6 years ago

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

    • Chanleakna123's avatar
      Chanleakna123
      Post Prodigy
      6 years ago
      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's avatar
        JQuon
        Helper I
        6 years ago

        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.

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    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.

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    6 years ago

    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.