Best pratices for 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.?

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can try to use following calculate column formula, I manually filter out related list of keys from different tables and do average on lookup_date year field.

AVG Age Per Course =
VAR _kundeList =
    CALCULATETABLE (
        VALUES ( Lookup_Kunde[Kunde_key] ),
        FILTER (
            ALL ( Lookup_Kunde ),
            [Alter Anfang erster CAS] = EARLIER ( Lookup_Kunde[Alerts Anfang erster CAS] )
        )
    )
VAR _anfansdatumList =
    CALCULATETABLE (
        VALUES ( Data_aumeldungen[GPS Anfansdatum] ),
        FILTER ( ALL ( Data_aumeldungen ), [Kunde_key] IN _kundeList )
    )
RETURN
    AVERAGEX (
        FILTER ( ALL ( Looup_Date ), [GPS Anfansdatum] IN _anfansdatumList ),
        [Year]
    )

Regards,

Xiaoxin Sheng