CODEWITHSUNDEEP
powerbidaxdata-analysisbusiness-intelligence
Guissous Allaeddine
Guissous Allaeddine

Reputation: 445

How to filter data based on other table field in power bi

I have 3 Tables Company, Employee, Card as shown in the screenshot below; the tables have relations between them.

We can notice from the card table that not every company has a card, or multiple cards related to it,

enter image description here

Requirement

I want to create a donut-chart to show the number of companies based on if the company has at least one card or "NO" (no card).

Example here; 50% Hold a card and 40% NO.

companies: 1,5,6,7,10 (have cards)

companies: 2,3,4,8,9 (Don't)

My solution

I merged the card_ID column from the Card table to the Company table (left join with Card table as second),

then created a new column "Card_Holder" that indicates "YES" if the card_ID Column is not empty and "NO" if its empty.

but when I applied the changes, power bi found a duplicate ID in the Company Table which is true, because the above steps created more than one record for each company based on the card table.

My solution is not good and its not working, and probably I am thinking with excel.

What is the appropriate method to do that in power BI?

Upvotes: 0

Views: 2672

Answers (1)

HubertL
HubertL

Reputation: 19544

You can create a calculated column in Company that computes the number of rows in Cards that are related to that Company, and return Yes if there are more than 0 rows, and No otherwise :

HasCard = 
    IF(
        COUNTROWS( RELATEDTABLE( Card ) ) > 0,
        "Yes",
        "No")

Then use it as the legend of a pie chart which value is the count of Company[Company_Id]: enter image description here

Note : If you want to do it in PowerQuery, you can group Card by Company_ID before left join

Upvotes: 1

Related Questions