Power BI Help With Creating a Filter

[AB_34]

Hello, I have been trying to create a filter for my PowerBI report, but have been strugling for the past couple days. Here is the problem:

I want my filter to Identify Sites that qualify as a FreeWire Site; A FreeWire Site is any site that has atleast one Charger with a configured output = 200 kWh. Sites can have multiple chargers, but if one of the chargers has a configured output of 200 kWh the entire site is considered a free wire site and should include the data from all the chargers attached to that site. Here is a small subsety of data to hopefully make this question more clear.

Site Name	Charger ID	Configured Output (kWh)
Houston	AB-246	100
Porter	AB-222	75
Houston	AB-899	200
Wascana	AB-111	90
Atlanta	AB-676	200
Wascana	AB-999	200

Using this table as an example, if working properly my filter should give me back a list of site names that I can choose to filter a visual: FreeWire Site = Houston, Atlanta, Wascana

Thank you !

[Bibiano_Geraldo · Accepted solution]

HI @AB_34 

Use this DAX formula to create a column that flags whether a site qualifies as a FreeWire Site:

Is FreeWire Site = 
IF(
    CALCULATE(
        MAX('Table'[Configured Output (kWh)]), 
        ALLEXCEPT('Table', 'Table'[Site Name])
    ) >= 200,
    "Yes",
    "No"
)

Drag the Is FreeWire Site column into the slicer.
Select "Yes" in the slicer to filter your visuals to display only FreeWire Sites

[Anonymous · Accepted solution]

Hi @AB_34 

Thanks for the reply from lbendlin and Bibiano_Geraldo , please allow me to provide another insight:

You can also try the following Measure

IsFreeWireSite = 
VAR _SiteName = CALCULATE(MAX([Site Name]), FILTER(ALLEXCEPT('Table', 'Table'[Site Name]), [Configured Output (kWh)] = 200))
RETURN
IF(MAX([Site Name]) = _SiteName, 1, 0)

Output:

Best Regards,
Yulia Xu

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

[Bibiano_Geraldo · Accepted solution]

HI @AB_34 

Use this DAX formula to create a column that flags whether a site qualifies as a FreeWire Site:

Is FreeWire Site = 
IF(
    CALCULATE(
        MAX('Table'[Configured Output (kWh)]), 
        ALLEXCEPT('Table', 'Table'[Site Name])
    ) >= 200,
    "Yes",
    "No"
)

Drag the Is FreeWire Site column into the slicer.
Select "Yes" in the slicer to filter your visuals to display only FreeWire Sites

[lbendlin]

You can do this without code

[AB_34]

Thanks for the response. Would this ensures that no data from the chargers at FreeWire Sites is excluded, even if some chargers do not individually meet the configured output criteria?

Example Walkthrough 

Using the provided data:

Site Name       Charger ID    Configured Output (kWh)    IsFreeWireSite

Houston	AB-246	100	1
Porter	AB-222	75	0
Houston	AB-899	200	1
Wascana	AB-111	90	1
Atlanta	AB-676	200	1
Wascana	AB-999	200	1

• For Houston, since AB-899 has a configured output of 200 kWh, the entire site (including AB-246) is marked as a FreeWire Site.
• The same logic applies to Wascana and Atlanta.