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

[techsfaction]

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,

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? 

[v-henryk-mstf]

Hi @techsfaction ,

Very much approve the solution given by @MFelix  If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[MFelix · Accepted solution]

Hi @techsfaction ,

In this case you have 50% companies with cards and 50% without cards so you can do two measures like this:

Cards = calculate(DISTINCTCOUNT(Companies[Company_ID]), Companies[Company_ID] in VALUES(Cards[Company_id]))

No Cards = calculate(DISTINCTCOUNT(Companies[Company_ID]),NOT( Companies[Company_ID] in VALUES(Cards[Company_id])))

Result below:

The model is the same has you have.

Another option is to create a calculated column on the Companies on your model using dax or Power Query:

DAX:

Cards = if(CALCULATE(COUNT(Cards[Card_id])) > 0, "Card", "No Card")

Query Editor:

• Merge tables Companies with cards
• Expand but select the count of non blank:

• Add a custom column or a replace values to get Card No Card
• In my case I used the replaced with the followiung syntax:

= Table.ReplaceValue(#"Renamed Columns",each [CardNCard] , each if [CardNCard] = 0 then "No Card" else "Card" ,Replacer.ReplaceValue,{"CardNCard"})

Now you can use the ID company on the visualization and the card no card column has legend:

Check PBIX attach.