cancel
Showing results for 
Search instead for 
Did you mean: 
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
Microsoft

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.