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 NUMBER PRODUCT COUNTRY CODE 1 O...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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

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