Add filter based on value to formula

[flipfis]

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 🙂

[Fowmy]

@flipfis 

Try this measure

Customers = 

COUNTROWS(
    DISTINCT(
        UNION( 
            CALCULATETABLE(
                VALUES(PlanA[id]),
                PlanA[status] = "active"
            ),
             CALCULATETABLE(
                VALUES(PlanB[id]),
                PlanB[status] = "active"
            )
        )
    )
)

[flipfis]

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]

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.

[Fowmy]

@flipfis 

I have not tested with Q&A. Once you combine the two tables, you still have those original tables which could be used by the Q&A engine. I think you should try to combine the tables in Power Query or at the source level if Q&A is your concern.

You can also try setting up synonyms in the table properties to handle questions in Q&A.