Getting Count by Country for Import and Export shipments

[CA8172]

I have many rows (20K+) over 5 years and many columns (30+) of data. Below is a simpler sample of data and then the request I am looking for. 

I have a list of shipments to the country (import) and from another country (export). What I would like is to show the Country name and how many imports they have had in the Import_Country column as well as how many they exported in the Ship_from_Country column. 

If anyone could provide assistance with formulas to separate these to the specific country, I would appreciate it. I have created two separate tables for just the country name (one for import and one for export), if necessary. 

[Idrissshatila · Accepted solution]

Hello @CA8172 ,

so what you do first is have a new table that will work as a dimension that has all the country names available without duplication.

then you connect the column in this table to both of your country columns, you will result 2 relationships one active and one inactive.

make sure the active relationship will be with the import so the scenario I attached for you would work as yours.

Then use the following measures (Make sure to replace the table and column names to match the ones you have)

Total Import = COUNTROWS('Fact Table')

Total Exports = CALCULATE(COUNTROWS('Fact Table'),USERELATIONSHIP(Dim_coutnry[Country],'Fact Table'[Ship_From_Country]))

and then use the country column from the dim country along with both of new measures that I attached for you.

Check the attached file below

[Anonymous · Accepted solution]

Hi, @CA8172 

Thanks @Idrissshatila and @Ritaf1983 , I have the following additions. Based on your description, I used the sample data from your image:

I use summarize to count Import country and Export country, my DAX expression is as follows:

Table =
VAR _Export =
    SUMMARIZE (
        ShipmentData,
        [Ship_From_Country],
        "Total Exports",
            COUNTAX (
                FILTER ( ShipmentData, [Ship_From_Country] <> BLANK () ),
                [Ship_From_Country]
            )
    )
VAR _import =
    SUMMARIZE (
        ShipmentData,
        [Import_Country],
        "Total Imports",
            COUNTAX (
                FILTER ( ShipmentData, [Import_Country] <> BLANK () ),
                [Import_Country]
            )
    )
VAR _c =
    SUMMARIZE (
        UNION (
            SELECTCOLUMNS ( _import, "country", [Import_Country] ),
            SELECTCOLUMNS ( _Export, "country", [Ship_From_Country] )
        ),
        [country]
    )
RETURN
    ADDCOLUMNS (
        _c,
        "Total Export", MAXX ( FILTER ( _Export, [Ship_From_Country] = [country] ), [Total Exports] ),
        "Total Import", MAXX ( FILTER ( _import, [Import_Country] = [country] ), [Total Imports] )
    )

This DAX expression is explained as follows:
1. In var _import and var _Export, use summarize to summarize the columns [Import_Country] and [Ship_From_Country], and use COUNTAX to count the non-empty items in these two columns. These two var variables return corresponding summary tables respectively.
2. In var c, use the UNION function to merge the [Import_Country] column and [Ship_From_Country] of the _import table and _Export table. Then summarize the merged countries.
3. Add Total Exports and Total Import columns to table c in the final ADDCOLUMNS.

The result of executing this DAX expression is as follows:

Using these columns in table visual, the effect is as follows:

I have provided the PBIX file used this time below. If it can help you, that would be great. If you have any questions about the DAX above, please let me know and I will do my best to answer them for you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Idrissshatila · Accepted solution]

Hello @CA8172 ,

so what you do first is have a new table that will work as a dimension that has all the country names available without duplication.

then you connect the column in this table to both of your country columns, you will result 2 relationships one active and one inactive.

make sure the active relationship will be with the import so the scenario I attached for you would work as yours.

Then use the following measures (Make sure to replace the table and column names to match the ones you have)

Total Import = COUNTROWS('Fact Table')

Total Exports = CALCULATE(COUNTROWS('Fact Table'),USERELATIONSHIP(Dim_coutnry[Country],'Fact Table'[Ship_From_Country]))

and then use the country column from the dim country along with both of new measures that I attached for you.

Check the attached file below

[Ritaf1983]

Hi @CA8172 

Hi  

In the first step, you need to create a dimension table with distinct countries of import/export.

Dax formula for the table :

Countries =

DISTINCT(UNION (

    SELECTCOLUMNS (

        'Table',

        'Table'[Ship_From_Country]

    ),

    SELECTCOLUMNS (

      'Table','Table'[Import_Countrv]

)))

Then create a relationship between this table and the transactions table.
. active + inactive

Next step is to create 2 measures using a play dimensions logic + userelationship  DAX function 

Total_imports = DISTINCTCOUNT('Table'[Shipment_N])

Total_exports = CALCULATE(DISTINCTCOUNT('Table'[Shipment_N]),USERELATIONSHIP(Countries[Country],'Table'[Ship_From_Country]))

Put the countries from the dimension table + 2 measures on the needed visual:

I attached the pbix you can follow the steps.

More information about play role dimensions is here :

https://www.youtube.com/watch?v=2BxaUXlx3K4

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly