Forum Discussion

akp's avatar
akp
Frequent Visitor
9 months ago
Solved

Slicers on Aggregated Measures

Hi,
I have a table with following example

DateTimeAreaCountsAmounts
1/1/202310:00Dallas123
1/2/202311:00Austin134
1/3/202312:00Boston15
1/4/202313:00Boston13
1/5/202314:00Boston13
1/6/202315:00Dallas11

 

The user needs to see the slicers for counts and amounts on the aggregated data based on City as seen below.

 

I created a calculated table summarized by City. Having slicers on the values from this table gives me the Min/Max Counts and Amounts by City which is good.

Now the challenge is user wants to search based on date range, counts and amounts as range. 

Say what are the cities between 1/4 to1/5 with 1- 2 counts and 11-25 amount?


If I add the date field to my calculated table, my slicer values are disaggregated. Nor I can use the selected values from the date slicer in my calculated table. 

Can anyone please help?

 



Need Help
  • DataNinja777's avatar
    DataNinja777
    9 months ago

    Hi akp ,

     

    In order to achive your required output, you can set the disconnected tables for count and amount ranges while keepoing the relationship between your fact table and the calendar table. Then, the key to solving the problem is to create a measure to put on the visual filter like below:

    Filter matches = 
-- Get current slicer values
VAR MinCount = MIN('Slicer_Counts'[Value])
VAR MaxCount = MAX('Slicer_Counts'[Value])
VAR MinAmount = MIN('Slicer_Amounts'[Value])
VAR MaxAmount = MAX('Slicer_Amounts'[Value])

-- Get current context values (which ALREADY respect the Date slicer)
VAR ActCount = [Total Counts]
VAR ActAmount = [Total Amounts]

RETURN
-- Check if current row's totals fall within selected disconnected ranges
IF(
    ActCount >= MinCount && ActCount <= MaxCount &&
    ActAmount >= MinAmount && ActAmount <= MaxAmount &&
    NOT ISBLANK(ActCount),
    1,
    0
)

    You can put the measure above to "Filter on this visual" and set Show items when the value "is" "1", and click Apply filter. 

    The resultant output is responsive to your range selection in the disconnected slicers of the amount range and count range as shown below:

    I have attached an example pbix file for your reference.

     

    Best regards,

     

4 Replies

  • v-sgandrathi's avatar
    v-sgandrathi
    Icon for Community Support rankCommunity Support
    9 months ago

    Hi akp,

     

    Thank you DataNinja777 for your response and the pbix file shared.

    Has your issue been resolved?
    If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone. 

    Thank you for your understanding!

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    9 months ago

    Hi akp ,

     

    In order to achive your required output, you can set the disconnected tables for count and amount ranges while keepoing the relationship between your fact table and the calendar table. Then, the key to solving the problem is to create a measure to put on the visual filter like below:

    Filter matches = 
-- Get current slicer values
VAR MinCount = MIN('Slicer_Counts'[Value])
VAR MaxCount = MAX('Slicer_Counts'[Value])
VAR MinAmount = MIN('Slicer_Amounts'[Value])
VAR MaxAmount = MAX('Slicer_Amounts'[Value])

-- Get current context values (which ALREADY respect the Date slicer)
VAR ActCount = [Total Counts]
VAR ActAmount = [Total Amounts]

RETURN
-- Check if current row's totals fall within selected disconnected ranges
IF(
    ActCount >= MinCount && ActCount <= MaxCount &&
    ActAmount >= MinAmount && ActAmount <= MaxAmount &&
    NOT ISBLANK(ActCount),
    1,
    0
)

    You can put the measure above to "Filter on this visual" and set Show items when the value "is" "1", and click Apply filter. 

    The resultant output is responsive to your range selection in the disconnected slicers of the amount range and count range as shown below:

    I have attached an example pbix file for your reference.

     

    Best regards,

     

  • akp's avatar
    akp
    Frequent Visitor
    9 months ago

    Thank you DataNinja777 This looks good. 
    The only issue I imagine with the above solution is this. I will not be able to show the min and max on the slicers that are coming out of an aggregation from region. Rather I am showing a random value from generated series for the measures. 

    The idea is that the slicers should be able to give the user and info on what the min and max value ranges are in my dataset and start analyzing from there. 

    May be the Slicer_Amounts and Slicer_Counts should also be a calculated tables aggregated at region ?



    • DataNinja777's avatar
      DataNinja777
      Icon for Super User rankSuper User
      9 months ago

      Hi akp ,

       

      The pain point you described is due to the fact that calculated tables are static once calculated and doesn't respond to slicer selections.  In order to address this issue, you can create two separate measures and then put them in card visual to indicate the min and max of the count range which responds to the slicer selection.

      Info_ActualMinCount = MINX( VALUES('FactData'[Area]), [Total Counts] )
      Info_ActualMaxCount = MAXX( VALUES('FactData'[Area]), [Total Counts] )

       The resultant output is shown in the two card visual as shown below:

       

      I have attached an example pbix file for your reference.

       

      Best regads,