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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Employee
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.