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 C...
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
MiguelSaldana77's avatar
MiguelSaldana77
Resolver I
6 years ago

Thanks smpa01 

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

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