Create Area Diagram

[MHazib]

Hi Community,

i am trying to create an area diagram that sums up every active certification in my DB. Therefore there is one table with all the certifications and one DateTable as follows:

Certificates Table:

Date Table:

Table relationships are the following:

I was able to create an area chart showing the total of certificates per day by creating calculated columns for each type of certificate in the date table:

ISO 14001 = CALCULATE(DISTINCTCOUNT(PartnerWithIso[PartnerID]),FILTER(ALL(PartnerWithIso),PartnerWithIso[ISO14001ValidToDate] >= 'Aufgerufene Funktion'[Date] && NOT ISBLANK(PartnerWithIso[ISO14001ValidToDate])))

My problem now is that I want to click on a specific date in the chart (e.g. 2024-05-03) and get back the PartnerIDs that have an active certification on that date. Is there any way to make this work?

[v-shex-msft]

HI @MHazib,

Perhaps you can try to do unpivot column on these fields to attribute and value, then you can use the value field as a relationship key to link to other tables. (attribute to chart legend as the group) They should help power bi do analysis with your records.

Unpivot columns - Power Query | Microsoft Docs

Regards,

Xiaoxin Sheng