Forum Discussion

AB_34's avatar
AB_34
Frequent Visitor
1 year ago
Solved

Power BI Help With Creating a Filter

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 NameCharger IDConfigured Output (kWh)
HoustonAB-246100
PorterAB-22275
HoustonAB-899200
WascanaAB-11190
AtlantaAB-676200
WascanaAB-999200


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 !

Need Help
Tips and Tricks
Tutorial Requests
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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's avatar
    Anonymous
    1 year ago

    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.

4 Replies

    • AB_34's avatar
      AB_34
      Frequent Visitor
      1 year ago

      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

      HoustonAB-246          100     1
      PorterAB-222           75     0
      HoustonAB-899          200     1
      WascanaAB-111           90     1
      AtlantaAB-676         200     1
      WascanaAB-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.



  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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's avatar
    Anonymous
    Not applicable
    1 year ago

    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.