How can I calculate this measures?

[mighty]

Good day

Kindly advise how can I approach this, I have Affordability table, VoluntaryTermination Table, LearnerTableFETerminations table and Non Collections Table. The problem I'm experiencing relates to the 'Affordability' table's income band. The 'Financial Exclusion' and 'Voluntary Terminations' sections point to their own tables, which each use unique dates, whereas the 'Affordability' table uses a different date. Consequently, when I use a calculated date table to link the three tables—for instance, the 'FE' and 'VT' measures do not connect with the 'Affordability' table to display results based on income band. Furthermore, when I attempt to create a bridge table that consolidates all data into a single table, the challenge arises of selecting which date to use for connection with the date table, given that each table has its distinct dates. Which other options I can use to be able to connect all these tables and be able to link income band with the measures I calculated, e.g. refer to the two dax measures below:

= Table.AddColumn(#"Replaced Value", "Income Band", each if [Total_Income] > 110000 then "R110 000 and above" else if [Total_Income] > 90000 then "R90 000 - R110 000" else if [Total_Income] > 70000 then "R70 000 - R90 000" else if [Total_Income] > 50000 then "R50 000 - R70 000" else if [Total_Income] > 20000 then "R20 000 - R50 000" else "R0 - R20 000")

Voluntary Terminations =

CALCULATE(

    DISTINCTCOUNT(All_Data[VoluntaryTerminations.ID Learner FK]), All_Data[VoluntaryTerminations.Date Termination Completed]<>BLANK()

)

Financial Exclusion =

CALCULATE(

    DISTINCTCOUNT(All_Data[LearnerTableFETerminations.ID]),All_Data[LearnerTableFETerminations.Date FE Completed]<>BLANK()

)

The data model:

[v-yilong-msft · Accepted solution]

Hi, @mighty ,

It seems you are facing a common challenge in data modeling, when dealing with multiple date fields from different tables in Power BI.

I give a simple example. Firstly I create a date table and a table.

Then I use the USERELATIONSHIP DAX to create a new measure.

MEASURE =
CALCULATE (
    SUM ( 'Table'[Sales Amount] ),
    USERELATIONSHIP ( 'Table'[New date], 'Dates'[Date] )
)

You can also use USERELATIONSHIP DAX in your measure.

Voluntary Terminations =
CALCULATE (
    DISTINCTCOUNT ( All_Data[VoluntaryTerminations.ID Learner FK] ),
    USERELATIONSHIP ( All_Data[VoluntaryTerminations.Date Termination Completed], DateTable[Date] ),
    All_Data[VoluntaryTerminations.Date Termination Completed] <> BLANK ()
)

You can read this document for a further study: USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.