Power BI Exchange

Expand all | Collapse all

Multiple filters in Dax formula

  • 1.  Multiple filters in Dax formula

    Posted 03-12-2018 11:06 AM
    Hello,

    I'm new to Power BI / Dax, mostly learning through PUG and other online sources.

    A colleague of mine is working with project data and would like to create a chart with projects on the x-axis, and actual project cost as the values on the y-axis. However, he does not want to show projects where there is no actual cost AND the work status has been "completed" or "cancelled." He cannot simply filter out 0 cost projects because that would remove new projects that have just started, from the chart.

    Unfortunately, i do not have access to his data, otherwise i would share the pbix. Actual Project Cost and Work Status are columns within the data, there's about 5-6 different work status descriptors to choose from.

    I'm having trouble creating the formula and would like some help. I initially tried working with FILTER( and AND( functions, but couldn't wrap my head around it. Any support would be greatly appreciated!

    Thank you,
    Tyler


    ------------------------------
    Tyler Wright
    ------------------------------


  • 2.  RE: Multiple filters in Dax formula

    Bronze Contributor
    Posted 03-12-2018 12:37 PM
    ​Hi @Tyler Wright

    I would look to use  an iterator function such as SUMX and then ​​in the expression section  if the status is "completed" or "cancelled" and the project cost is 0 return BLANK() otherwise return the project cost.


    I assume that there is a 1:Many  relationship between status and project cost.

    Regards
    Anthony


    ------------------------------
    Anthony Bulk
    Sierra Systems
    @JAnthonyBulk
    ------------------------------



  • 3.  RE: Multiple filters in Dax formula

    Silver Contributor
    Posted 03-12-2018 04:56 PM
    Edited by David Yee 03-12-2018 04:57 PM

    Hi Tyler,

    I was just working on this same problem.  For my problem, I wanted to filter every GL code that didn't have revenue or expenses listed.  I was able to fix this problem with the following code:

    Filter = IF(ISBLANK([Rev2])&&ISBLANK([Labor]),0,1)

    After I created my measure, I added it as a filter and set it to "is not" zero and it worked for me.  I think yours is a little harder but the same concept should work.  I would assume the code is something like..

    Filter = IF(AND(ISBLANK([ActualCost]),[Work]=Completed||[Work]="Cancelled"),0,1)




    ------------------------------
    David Yee
    Foothill Packing
    Salinas CA
    ------------------------------



  • 4.  RE: Multiple filters in Dax formula

    Gold Contributor
    Posted 03-12-2018 09:20 PM
    David,

    Did you create a measure or a calculated column (out of curiosity).

    This is a great solution as well.
    This essentially flags the records you want to filter.
    I do this a lot for complicated date logic because we use a non-standard calendar and can't use all the nifty native time intelligence functions.


    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    New Seasons Market
    Portland OR
    ------------------------------



  • 5.  RE: Multiple filters in Dax formula

    Silver Contributor
    Posted 03-13-2018 11:38 AM
    I used a measure.  I try to avoid calculated columns if I can.

    ------------------------------
    David Yee
    Foothill Packing
    Salinas CA
    ------------------------------



  • 6.  RE: Multiple filters in Dax formula

    Gold Contributor
    Posted 03-12-2018 09:14 PM
    Couple of ways to do this:

    1. Add filters to the visual itself.
    2. Filter a measure.
        a. Make a simple measure Actual Project Cost = SUM(Actual Project Cost column), if you haven't already.
        b. CALCULATE([Actual Project Cost], FILTER(....

    How you write the Calculate with filter depends on if the two column you need to filter are in the same table.
    If they are, you can use something like this (I had to guess for the positive statuses).

    CALCULATE([Actual Project Cost], FILTER(tablename, tablename[actual project cost column] <> 0 && tablename[Project Status] IN {"Active", "Pending"}))

    If they come from different tables, then you can nest the FILTERS:

    CALCULATE( measure, FILTER(table1, table1[column] <> 0), FILTER(table2, table2[Column] IN {list of statuses}))

    Here is a link to a post showing it done both ways:
    https://community.powerbi.com/t5/Desktop/DAX-sum-with-two-filters/td-p/44039

    And here is a more comprehensive write up:
    https://www.sqlbi.com/articles/filter-arguments-in-calculate/

    Hope that helps.
    Audrey

    ------------------------------
    Audrey Abbey
    SR. BI Developer/Analyst
    New Seasons Market
    Portland OR
    ------------------------------



  • 7.  RE: Multiple filters in Dax formula

    Gold Contributor
    Posted 03-13-2018 10:48 AM
    Greetings @Tyler Wright:

    I 100% agree with @Audrey Abbey - use a calculate function and append the filter logic on the back end.

    The following resources will GREATLY aid you in your learning of PowerBI - Data modeling and the Business Intelligence Language


    Thanks!
    William


    ------------------------------
    William Rodriguez
    Business Analyst
    ------------------------------



  • 8.  RE: Multiple filters in Dax formula

    Posted 03-13-2018 10:52 AM
    Thank you everyone for these helpful tips! I'm going to sit down with some of the suggestions and see what sticks. I will report back with my results.

    Thanks for the fantastic support!!


    ------------------------------
    Tyler Wright
    ------------------------------