Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mighty
Frequent Visitor

How can I calculate this measures?

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:

mighty_1-1710236414870.png

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

vyilongmsft_0-1710314267942.png

vyilongmsft_1-1710314474419.png

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

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

vyilongmsft_2-1710314632808.png

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.

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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.

vyilongmsft_0-1710314267942.png

vyilongmsft_1-1710314474419.png

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

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

vyilongmsft_2-1710314632808.png

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.