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

[mapko]

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))

[v-ljerr-msft]

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.

Regards

[mapko]

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