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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
nataliesmiy1357
Helper III
Helper III

Two tables - creating a measure/column

Hello!  I have a quick question... I have two tables that are connected by a many to many relationship.  I am trying to create some measures of hand calculations but I'm a bit stuck.

 

This is what I have right now....  Two tables:  Downtime Report and OR Report

 

This table includes: Downtime Report[Date], Downtime Report[Plant and Line], OR Report [ Planned OR%], OR Report [Line Result], Downtime Report[ Total Assembly DT Min], Downtime Report[Line Stop Downtime - NAM Ops - Operations DT Mins], Downtime Report[% of DT Minutes]

 

I now need to create a complicated formula to create a new percentage, but it needs the percentages from the OR Report table, and the % of DT Minutes from the Downtime Report.  Here is the formula that I'd need to create:

 

Adjusted OR:

= if(

if(( OR Report [Line Result] - OR Report [ Planned OR%]) < 0 , OR Report [Line Result] - (OR Report [Line Result]- OR Report [ Planned OR%]) * Downtime Report[% of DT Minutes] , OR Report [Line Result] + (OR Report [Line Result] - OR Report [ Planned OR%]) * Downtime Report[% of DT Minutes]) > 100%, 100%,

if (( OR Report [Line Result]- OR Report [ Planned OR%]) < 0, OR Report [Line Result]- (OR Report [Line Result]- OR Report [ Planned OR%]) * Downtime Report[% of DT Minutes], OR Report [Line Result]+ (OR Report [Line Result]- OR Report [ Planned OR%]) * Downtime Report[% of DT Minutes])

 

Is this possible??

 

nataliesmiy1357_0-1704309473309.png

 

1 ACCEPTED SOLUTION
v-jingzhan-msft
Community Support
Community Support

Hi @nataliesmiy1357 

 

You may try using SELECTEDVALUE function to get the distinct value of each percentage used in the calculation. As these percentages are from two tables, it would be better to use a measure. 

For example (I only extract some part from your formula):

Adjusted OR =
IF (
    SELECTEDVALUE ( 'OR Report'[Line Result] ) - SELECTEDVALUE ( 'OR Report'[ Planned OR%] ) < 0,
    SELECTEDVALUE ( 'OR Report'[Line Result] ) - ( SELECTEDVALUE ( 'OR Report'[Line Result] ) - SELECTEDVALUE ( 'OR Report'[ Planned OR%] ) ) * SELECTEDVALUE ( 'Downtime Report'[% of DT Minutes] ),
    SELECTEDVALUE ( 'OR Report'[Line Result] ) + ( SELECTEDVALUE ( 'OR Report'[Line Result] ) - SELECTEDVALUE ( 'OR Report'[ Planned OR%] ) ) * SELECTEDVALUE ( 'Downtime Report'[% of DT Minutes] )
)

or

Adjusted OR =
VAR vLineResult = SELECTEDVALUE ( 'OR Report'[Line Result] )
VAR vPlannedORPct = SELECTEDVALUE ( 'OR Report'[ Planned OR%] )
VAR vDTMinutesPct = SELECTEDVALUE ( 'Downtime Report'[% of DT Minutes] )
RETURN
    IF (
        vLineResult - vPlannedORPct < 0,
        vLineResult - ( vLineResult - vPlannedORPct ) * vDTMinutesPct,
        vLineResult + ( vLineResult - vPlannedORPct ) * vDTMinutesPct
    )

 

Here are some docs for your reference:

SELECTEDVALUE function - DAX | Microsoft Learn

VAR keyword (DAX) - DAX | Microsoft Learn

Use variables to improve your DAX formulas - DAX | Microsoft Learn

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

View solution in original post

1 REPLY 1
v-jingzhan-msft
Community Support
Community Support

Hi @nataliesmiy1357 

 

You may try using SELECTEDVALUE function to get the distinct value of each percentage used in the calculation. As these percentages are from two tables, it would be better to use a measure. 

For example (I only extract some part from your formula):

Adjusted OR =
IF (
    SELECTEDVALUE ( 'OR Report'[Line Result] ) - SELECTEDVALUE ( 'OR Report'[ Planned OR%] ) < 0,
    SELECTEDVALUE ( 'OR Report'[Line Result] ) - ( SELECTEDVALUE ( 'OR Report'[Line Result] ) - SELECTEDVALUE ( 'OR Report'[ Planned OR%] ) ) * SELECTEDVALUE ( 'Downtime Report'[% of DT Minutes] ),
    SELECTEDVALUE ( 'OR Report'[Line Result] ) + ( SELECTEDVALUE ( 'OR Report'[Line Result] ) - SELECTEDVALUE ( 'OR Report'[ Planned OR%] ) ) * SELECTEDVALUE ( 'Downtime Report'[% of DT Minutes] )
)

or

Adjusted OR =
VAR vLineResult = SELECTEDVALUE ( 'OR Report'[Line Result] )
VAR vPlannedORPct = SELECTEDVALUE ( 'OR Report'[ Planned OR%] )
VAR vDTMinutesPct = SELECTEDVALUE ( 'Downtime Report'[% of DT Minutes] )
RETURN
    IF (
        vLineResult - vPlannedORPct < 0,
        vLineResult - ( vLineResult - vPlannedORPct ) * vDTMinutesPct,
        vLineResult + ( vLineResult - vPlannedORPct ) * vDTMinutesPct
    )

 

Here are some docs for your reference:

SELECTEDVALUE function - DAX | Microsoft Learn

VAR keyword (DAX) - DAX | Microsoft Learn

Use variables to improve your DAX formulas - DAX | Microsoft Learn

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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