Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
flipfis
Frequent Visitor

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 🙂

4 REPLIES 4
Fowmy
Super User
Super User

@flipfis 

Try this measure

Customers = 

COUNTROWS(
    DISTINCT(
        UNION( 
            CALCULATETABLE(
                VALUES(PlanA[id]),
                PlanA[status] = "active"
            ),
             CALCULATETABLE(
                VALUES(PlanB[id]),
                PlanB[status] = "active"
            )
        )
    )
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

flipfis
Frequent Visitor

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? 

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.

@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.




Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors