Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mapko
Helper I
Helper I

DAX query to create a table from multiple columns from different tables

All,

 

I am trying to create a summary table that will contain the following columns

1) year

2) country (based on column from my country table)

3) flag 1 (based on a column from Fact table 1)

4) flag 2 (based on a column from Fact table 2)

5) flag 3 (based on a column from Fact table 3)

 

What I did so far with DAX are the first two steps. Script is below. I also have seperate calcualted tables for flags 1 - 3 but I can't seem to figure out a way to integrate it all together into a single DAX query. Flag 1-3 columns are joined to the main table (Year + country) on the Year + Country key.

 

Any thoughts?

 

Thank you in advance!

 

Main table:

 

Mapko =
CROSSJOIN(
DATATABLE(
"Year", INTEGER,
{
{2009},
{2010},
{2011},
{2012},
{2013},
{2014},
{2015},
{2016},
{2017}
}
),
SELECTCOLUMNS(FILTER(Countries, Countries[Status]<>"Active"), "Country", Countries[Name])
)

 

E.g. for the summary of Fact table 1: (same principle applied to Fact 2 and 3)

 

Summary_fact1 = SUMMARIZE(
'Fact1',
'Fact1'[Country],
'Fact1'[Year],
"Fact1Flag", if(ISBLANK(CALCULATE(COUNT('Fact1'[ID]), FILTER('Fact1', 'Fact1'[Type]="xyx"))),0,1))

2 REPLIES 2
v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @mapko,

 

Could you tell us the reason why you're trying to create this summary table? Do you have any trouble creating relationships between your Dim and Fac tables?

 

Based on my understanding, if the relationships are properly created in your mode, there could be some easier way to do it with Measures and Calculate Columns, instead of such a big summary table in most scenarios.

 

In addition, it's better to post your table structures(including the relationships) with some sample data and your expected result for getting better assistance on this issue.Smiley Happy

 

Regards

Hi @v-ljerr-msft,

 

Thank you for your reply. 

 

I don't have trouble creating relationships between my tables. I have 3 facts connected to a single dimension. I thought it would be easier to summarize my results by country/year since I have to compare entries in each flag field to come up with some insights I derive in a calculated column (e.g. text below).

Example:

Country - year - flag 1 - flag 2 - flag 3 - text

a - 2009 - 1 - 0 - 1 - in 2009 country a used system A. Country a also uses system c

a - 2010 - 1 - 1 - 1 - in 2010 country a used system a, b, and c

b - 2009 - 0 - 0 - 1 in 2009 country b only used system c

 

Also, I want to limit my data to a subset of years that come from my facts.

 

This is the best I can give you as I unfortunately cannot share any model/dummy data.

 

But I am open to your suggesstion if you think there is a better way to resolve this by measures/Calculated columns

 

Regards

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors