Count values from Many to One relationship with a condition

[Bia]

Hello!

I am new to Power BI and I have been struggling with letting go from the Excel mentality... I have a Table 1 that looks something like this:

SHIPMENT NUMBER	PRODUCT	COUNTRY CODE
1	Orange	1234
6	Orange	1567
1	Apple	1234
7	Grapes	1234
8	Apple	8907

And it is related to another table (Table 2) by the COUNTRY CODE, but in this table there is only a row for each code, for example:

COUNTRY CODE

1234

8907

1567

9876

So they have a Many to One relationship

I need to create a column in Table 2 (the ONE side of the relationship) that has the COUNT OF DISTINCT Shipments for each of the country codes, but WITH THE CONDITION of only counting the shipments that have Oranges:

COUNTRY CODE	ORANGE SHIPMENTS
1234	5
1567	7

I can't only filter the visuals because I need the total value for each country so I can then validate the number of shipments by an If statement on another column of "If it is higher than 10, then 0.2, if not 0"... and then use the value for more calculations.

I have tried the calculate function in the Table 2 column, but it did not seem to work, it returned no value at all:

Orange Shipments= CALCULATE(
    DISTINCTCOUNT('Table 1'[Shipment Number]),
    'Table 1'[Product]= "Orange")

Please, how can I summarize the count of shipments with oranges by country? This has been making me crazy.

Thank you! 

[Jihwan_Kim · Accepted solution]

Hi,

Please try the below. It is for creating a new column.

Orange shipments count CC =
COUNTROWS (
    SUMMARIZE (
        FILTER ( RELATEDTABLE ( 'Table1' ), 'Table1'[PRODUCT] = "Orange" ),
        'Table1'[SHIPMENT NUMBER]
    )
)

[Anonymous · Accepted solution]

Hi @Bia ,

Please check this formula:

Column =
CALCULATE (
    DISTINCTCOUNT ( 'Table 1'[SHIPMENT NUMBER] ),
    FILTER (
        'Table 1',
        'Table 1'[COUNTRY CODE] = 'Table 2'[COUNTRY CODE]
            && 'Table 1'[PRODUCT] = "Orange"
    )
)

Best Regards,

Jay

[Jihwan_Kim · Accepted solution]

Hi,

Please try the below. It is for creating a new column.

Orange shipments count CC =
COUNTROWS (
    SUMMARIZE (
        FILTER ( RELATEDTABLE ( 'Table1' ), 'Table1'[PRODUCT] = "Orange" ),
        'Table1'[SHIPMENT NUMBER]
    )
)