CODEWITHSUNDEEP
powerbidaxdatediff
Sander De Weert
Sander De Weert

Reputation: 51

Power-BI DATEDIFF from 2 different dimension tables, related to fact table

This fact table is central. I'd like to create a measure with a DATEDIFF between the "StartVerkoopdatum" date and the "KAOOndertekend" date, related to this fact. Every fact line get's a value DATEDIFF(StartVerkoopdatum, KAOOndertekend, DAY).

The red lines are the connected fields. The blue arrows are the values I'd like to use in the datediff.

Is it possible to create a measure in the fact, that wil get this value?

Table structure

Upvotes: 0

Views: 681

Answers (1)

smpa01
smpa01

Reputation: 4346

Stack strongly recommends that you provide the sample data in table format and not picture.

Use the following measure

Measure =
DATEDIFF (
    CALCULATE (
        MAX ( INT_Subproject[StartVerkoopdatum] ),
        //CROSSFILTER reverses the filter direction on-the-fly
        //to retrieve the MAX of dim by fact
        CROSSFILTER ( factINT[ProjID], INT_Subproject[ProjID], BOTH )
    ),
    CALCULATE (
        MAX ( INT_Hoofdk[KAOOndertekend] ),
         //CROSSFILTER reverses the filter direction on-the-fly
        //to retrieve the MAX of dim by fact
        CROSSFILTER ( factINT[HoofID], INT_Hoofdk[HoofID], BOTH )
    ),
    DAY
)

Upvotes: 2

Related Questions