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

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

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