Forum Discussion

flipfis's avatar
flipfis
Frequent Visitor
5 years ago

Add filter based on value to formula

Hi! I'm new to Power BI and I have a question regarding a formula. 

 

I have two queries (via web, JSON) with two different subscriptions we offer. In the datasheets, there is a column with "Status" which tells us whether a membership is active or canceled. I have the following formula to get all the customer_id's but I'd like to build a filter in the formula since I only want to show the active memberships (or maybe use a completely different formula :p) 

 

BothMemberships = DISTINCT(UNION(ALL('PlanA'[customer_id]),ALL('PlanB'[customer_id])))

 

Anyone that can help me out? Thanks šŸ™‚

Need Help

4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    flipfis 

    Try this measure

    Customers = 

COUNTROWS(
    DISTINCT(
        UNION( 
            CALCULATETABLE(
                VALUES(PlanA[id]),
                PlanA[status] = "active"
            ),
             CALCULATETABLE(
                VALUES(PlanB[id]),
                PlanB[status] = "active"
            )
        )
    )
)
    • flipfis's avatar
      flipfis
      Frequent Visitor
      5 years ago

      Thank you! Work's perfect! But there is happening something weird now. In the measure you created, I get a total row of 1161. But when I use a Q&A and ask "Count active PlanA members" and a different Q&A for "Count active PlanB members", the total of those two combined is 1313. 

       

      I understand this can have many reasons, but do you have a suggestion? 

      • v-jingzhang's avatar
        v-jingzhang
        Community Support
        5 years ago

        Hi flipfis 

         

        I think Fowmy 's measure gets the correct result for the number of distinct count. I guess that a possible cause is that you have multiple same IDs in TableA and TableB. When you count the distinct number for each table separately, they are counted in each result. But if you combine two tables first, then count the distinct number, duplicate values will be excluded so that you will get a number which is smaller than the total of adding two separate tables' counts. Check the data in TableA and TableB to find out if there are some common IDs.

         

        Hope this helps.

         

        Regards,
        Community Support Team _ Jing
        If this post helps, please Accept it as the solution to help other members find it.