Forum Discussion

Bia's avatar
Bia
New Member
4 years ago
Solved

Count values from Many to One relationship with a condition

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 NUMBERPRODUCTCOUNTRY CODE
1Orange1234
6Orange1567
1Apple1234
7Grapes1234
8Apple8907

 

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 CODEORANGE SHIPMENTS
12345
15677

 

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! 

  • 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's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • 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's avatar
    Anonymous
    Not applicable

    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