Forum Discussion

Spaik's avatar
Spaik
Regular Visitor
2 years ago
Solved

segmented filter for customers

I hope this message finds you well.

We have encountered a challenge in Power BI that we have not been able to resolve on our own. Specifically, we are trying to filter and list customers whose "Metragem" (area measurement) is above 40% of a given reference value. The goal is to display a list of these customers within a visual, but we haven't succeeded so far.

We attempted to use DAX formulas, such as COUNTROWS, ALLSELECTED, and CONCATENATEX, but we are still struggling to achieve the desired outcome. We need the report to automatically identify and list these customers whenever the filters are applied.

Could you please assist us in resolving this issue? Any guidance or suggestions would be greatly appreciated.

Thank you in advance for your support.

Best regards,

Need Help
  • Kaviraj11's avatar
    Kaviraj11
    2 years ago

     

    You can create another column and put it in filter pane and select value as 1

    1. Create a Calculated Column for the 40% Threshold:

      • First, create a calculated column in your table to determine if the “Metragem” is above 40% of the reference value. 
        AboveThreshold = 
IF(
    'YourTable'[Metragem] > 0.4 * 'YourTable'[ReferenceValue],
    1,
    0
)

3 Replies

  • Kaviraj11's avatar
    Kaviraj11
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hi,

     

    Here’s a revised approach to clarify:

    1. Create a Measure for the Reference Value:

      ReferenceValue = SUM('Table'[Metragem]) / COUNTROWS('Table')

    2. Create a Measure for the 40% Threshold:

      ThresholdValue = [ReferenceValue] * 0.4

    3. Create a Measure to List Customers Above the Threshold: This measure directly lists the customers whose “Metragem” is above the threshold:

      CustomersAboveThreshold = 
CONCATENATEX(
    FILTER(
        VALUES('Table'[CustomerName]),
        CALCULATE(SUM('Table'[Metragem])) > [ThresholdValue]
    ),
    'Table'[CustomerName],
    ", "
)
    • Spaik's avatar
      Spaik
      Regular Visitor
      2 years ago

      that was really good! however, I received feedback as follows with the first two measurements.

      but, It brings all customers on the same field.

       

  • Kaviraj11's avatar
    Kaviraj11
    Icon for Solution Sage rankSolution Sage
    2 years ago

     

    You can create another column and put it in filter pane and select value as 1

    1. Create a Calculated Column for the 40% Threshold:

      • First, create a calculated column in your table to determine if the “Metragem” is above 40% of the reference value. 
        AboveThreshold = 
IF(
    'YourTable'[Metragem] > 0.4 * 'YourTable'[ReferenceValue],
    1,
    0
)