Forum Discussion

MiguelSaldana77's avatar
MiguelSaldana77
Resolver I
6 years ago
Solved

How Include elements of Catalogos without value

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

 

 

 

 

Need Help
  • smpa01's avatar
    smpa01
    6 years ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

  • MiguelSaldana77's avatar
    MiguelSaldana77
    Resolver I
    6 years ago

    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)

  • smpa01's avatar
    smpa01
    Community Champion
    6 years ago

    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

     

     

     

      • smpa01's avatar
        smpa01
        Community Champion
        6 years ago

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