Need help with data model

[Anonymous]

Hi everybody

I am modeling a set of csv files and have a question.

the table Lookup_Kunde contains details about customers that have subsribed to a course. The column highlighted in red, Alter Anfang erster CAS, explains the age of the customer when starting the course.

the table Lookup_Veranstaltung contains details about the courses offered. the column highlighted in red, GPS Name, states the title of the course.

Both lookup tables are consolidated in the table Data_Anmeldungen.

I want to make a graph with the following: average age per course. I am aware that I cannot do this with the current filtering directions. However, what would be the best practice in this case, if I want to maintain the star shape, and avoid cross-directional filtering? Which new Queries/tables should i make etc.?

[Anonymous]

Hi everybody

I am modeling a set of csv files and have a question.

the table Lookup_Kunde contains details about customers that have subsribed to a course. The column highlighted in red, Alter Anfang erster CAS, explains the age of the customer when starting the course.

the table Lookup_Veranstaltung contains details about the courses offered. the column highlighted in red, GPS Name, states the title of the course.

Both lookup tables are consolidated in the table Data_Anmeldungen.

I want to make a graph with the following: average age per course. I am aware that I cannot do this with the current filtering directions. However, what would be the best practice in this case, if I want to maintain the star shape, and avoid cross-directional filtering? Which new Queries/tables should i make etc.?

[v-danhe-msft]

Hi @Anonymous,

Could you please offer a sample data to have a test and post your desired result? If you just want to calculate the average age of per course, it seemed that you could create a measure to calculate and do not need to modify your data model, the relationship and crossfilter of your data model may help you to filter the different data in different table or related different data, so if you could post your desired resuly, maybe I could help more.

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft

Thank you for your help. Attached I add a *.pbix file with a simplified version of the same problem. I would like to keep my filter directions unidirectional. The desired result looks like this:

link to file: https://wetransfer.com/downloads/8671a2927afbe53159fe856788b473d420190122101759/e01898fadce0a289a28e...

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, if you do not want to change your relationship, you could refer to below calculated column:

Average = CALCULATE(AVERAGE(Lookup_Customer[Age]),FILTER('Data_Subscription','Data_Subscription'[Course_Key]='Lookup_Course'[Course_Key]))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He