new table

4 Topics

New table using values from two other tables
Hello, I am working on analysing businesses' financial statements and need some help with writing DAX code to make a new table using values from another table, and names from another table. I already posted a similar question (New column in new table based on values of other tables ) but my problem has gotten a bit more complex so the solution there doesn't apply to this. Maybe Mahesh0016 has an answer for this as well? 😄 Say I have two tables, Table 1 and Table 2. Table 1 (which has info on shop names, year, the original line item name from each shop's financial statements and the value corresponding to that line item) Shop name Year Line item name Value Shop A 2022 current assets total 10 Shop A 2022 non-current assets total 20 Shop A 2022 current liabilities 30 Shop B 2022 CA total 40 Shop B 2022 NCA total 50 Shop B 2022 CL total 60 Shop B 2021 CA total 70 Shop B 2021 NCA total 80 Shop B 2021 CL total 90 Table 2 (which has the original line item name and a new name for consistency) Line item name New name current assets total Total current assets CA total Total current assets non-current assets total Total non-current assets NCA total Total non-current assets current liabilities total Total current liabilities CL total Total current liabilities I want to use information from Tables 1 and 2 to ultimately get a table like this (let's call it Table 3): Table 3 Shop name Year Total current assets Total non-current liabilities Total current liabilities Shop A 2022 10 20 30 Shop B 2022 40 50 60 Shop B 2021 70 80 90 Many thanks in advance!
Solved
Anonymous
3 years ago Place DAX Commands and Tips
1.1KViews
0likes
4Comments
Select values from two tables and paste them into new table
So i want to create a new measure called Person Absence Date that would return absence dates for a person from two tables ( 'GAM Absence' and 'GAM AbsenceRequest'. I wanted to do something like this: Person Absence Dates = VAR selectedName = MAX('Issues'[Assignee]) VAR selectedUID = MAXX(FILTER('GAM Users', 'GAM Users'[name] = selectedName), 'GAM Users'[PersonID]) RETURN CONCATENATEX( FILTER('GAM Absence', 'GAM Absence'[UID] = selectedUID ) , 'GAM Absence'[Date] & ", ", , 'GAM Absence'[Date], ASC ) & CONCATENATEX( FILTER('GAM AbsenceRequest', 'GAM AbsenceRequest'[UID] = selectedUID ) , 'GAM AbsenceRequest'[Date] & ", ", , 'GAM AbsenceRequest'[Date], ASC ) This however returns a list of the dates (one row, dates seperated by commas), not dates in rows. How would i do that?
Anonymous
3 years ago Place DAX Commands and Tips
481Views
0likes
1Comment
Full outer join 2 tables similar to a 1=1 repeating values from B onto A
I have 2 tables, A and B. I want to full outer join data from B onto A. In Sql, I know how I would do i.e. FULL OUTER JOIN B on 1=1. I'm looking for the same in DAX. Examples of A, B and Outcome below. A Item Desc 3870 pink hat 45783 blue scarf 5427 red dress 648 yellow gloves B Product Ctgry wearable Cotton wearable Poly wearable Rubber wearable Hemp Outcome Item Desc Product Ctgry 3870 pink hat wearable Cotton 3870 pink hat wearable Poly 3870 pink hat wearable Rubber 3870 pink hat wearable Hemp 45783 blue scarf wearable Cotton 45783 blue scarf wearable Poly 45783 blue scarf wearable Rubber 45783 blue scarf wearable Hemp 5427 red dress wearable Cotton 5427 red dress wearable Poly 5427 red dress wearable Rubber 5427 red dress wearable Hemp 648 yellow gloves wearable Cotton 648 yellow gloves wearable Poly 648 yellow gloves wearable Rubber 648 yellow gloves wearable Hemp
Solved
gnatnug
4 years ago Place DAX Commands and Tips
741Views
0likes
2Comments
Average of Rates
I built out a table in powerBI to give me average of a series or rates. However, the average is wrong. I wrote out the new table like this: summarizecolumns(Sheet1[District],'Sheet1'[Fiscal Year],"Usage Rate",Average('Sheet1'[%Beds])) For example, district only has one hospital with a rate of 81.1% for a specific year. However, when I create the table for with "Usage Rate" by district for that specific year, the table reads 83.5. The table should have a rate fo 81.1 for one hospital in that district for that year . Since that average is wrong, I can safely assume the average is wrong for all other districts with multiple hospitals.
awashington6
4 years ago Place DAX Commands and Tips
947Views
0likes
4Comments