Count values from Many to One relationship with a condition

Bia — Tue, 07 Jun 2022 18:20:28 GMT

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!

Re: Count values from Many to One relationship with a condition

Jihwan_Kim — Wed, 08 Jun 2022 03:55:48 GMT

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] ) )

Re: Count values from Many to One relationship with a condition

Anonymous — Fri, 10 Jun 2022 08:53:27 GMT

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

topic Re: Count values from Many to One relationship with a condition in DAX Commands and Tips

Count values from Many to One relationship with a condition

Re: Count values from Many to One relationship with a condition

Re: Count values from Many to One relationship with a condition