How Include elements of Catalogos without value

[MiguelSaldana77]

Hi everyone,  I have 4 tables , one is the fact table and 3 dimension catalogue (Company, Country and Accounts)

Each Country and Accounts belong to a specific Company, that is why the catalogue of Country and Accounts have a column of company. 

I need to show in the dashboard the total filter company, but at the same time bring the respective countries and accounts of the company (like the image).

This is the project without_value

 

[smpa01]

@MiguelSaldana77 

I created 3 calculated columns as below

Company = RELATED(Company[Company_Name])
// Company has 1 to many relationship to Country

ID_Account = LOOKUPVALUE('Fact'[ID_Account],'Fact'[IdCountry],Country[IdCountry])

Sales =
IF (
    ISBLANK (
        LOOKUPVALUE (
            'Fact'[Total],
            'Fact'[ID_Account], Country[ID_Account],
            'Fact'[IdCountry], Country[IdCountry]
        )
    ),
    0,
    LOOKUPVALUE (
        'Fact'[Total],
        'Fact'[ID_Account], Country[ID_Account],
        'Fact'[IdCountry], Country[IdCountry]
    )
)

Now utilising everything from Country table I can get to to the view you desired

[MiguelSaldana77]

Thanks @smpa01 

I did something similar but we are missing the complete accounts by company

[smpa01 · Accepted solution]

@MiguelSaldana77  another one only by creating a table and you can get all your viz from here

 testtbl =
VAR _a1 =
    SELECTCOLUMNS (
        Country,
        "IdCountry", [IdCountry] + 0,
        "Desc_Country", [Desc_Country],
        "IdCompany", [IdCompany] + 0
    )
VAR _b1 =
    DISTINCT ( SELECTCOLUMNS ( Account, "ID_Account", [ID_Account] & "" ) )
VAR _c1 =
    CROSSJOIN ( _a1, _b1 )
VAR _d1 =
    SELECTCOLUMNS (
        Company,
        "IdCompany", [Id_Company] + 0,
        "Company_Name", [Company_Name]
    )
VAR _e1 =
    NATURALLEFTOUTERJOIN ( _c1, _d1 )
VAR _f1 =
    SELECTCOLUMNS (
        'Fact',
        "ID_Account", [ID_Account] & "",
        "IdCountry", [IdCountry] + 0,
        "Total", [Total]
    )
VAR _h1 =
    NATURALLEFTOUTERJOIN ( _e1, _f1 )
RETURN
    _h1

[smpa01]

@MiguelSaldana77  I tried the following

A. please create a new table as following

newtbl = 
VAR _a = SELECTCOLUMNS(Country,
                              "IdCountry", [IdCountry],
                              "Desc_Country", [Desc_Country],
                              "IdCompany",[IdCompany]
                       )
VAR _b = DISTINCT(
                  SELECTCOLUMNS(Account, "ID_Account",[ID_Account])
                  )
 VAR _c = CROSSJOIN(_a,_b)
 RETURN _c

 B. and two more calculated columns

Company = LOOKUPVALUE(Company[Company_Name],Company[Id_Company],newtbl[IdCompany])

Sales = LOOKUPVALUE('Fact'[Total],'Fact'[IdCountry],newtbl[IdCountry],'Fact'[ID_Account],newtbl[ID_Account],'Fact'[IdCountry],newtbl[IdCountry])

[MiguelSaldana77]

Thanks @Anonymous , I have already used that option but the problem is that show the complete elements of the catalogues (in both cases) (see the image).  I need to show just the respective Accounts and Countries of the companies (like the original image)

[Anonymous]

The Rows and Columns fields within a matrix have an option to "Show items with no data." In the Rows property, the "Desc_Country" field will have a down arrow, like a dropdown box arrow, on the right side. Click that. Choose the "Show items with no data" menu item. The same option will be on all your Row and Column fields.