Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Thanks @smpa01
I did something similar but we are missing the complete accounts by company
@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 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])
Thanks @RandyPgh , 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)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |