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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wek9294
Frequent Visitor

Conditional difference based on conditional values

Below is my data

 

Capture.PNG

 

What I'm trying to get is the cost difference from the rank 1 carrier against lines that don't have a rank.  So for line 2, it would calculate 150 - 100, and line 5 would calculate 900 - 100. My data would look like.

Capture2.PNG

 

Alternatively, it can look like this since I can filter out the non-blanks

 

Capture3.PNG

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@wek9294 , Try as a new column

 

new column =
var _min = minx(filter(Table, [lane] =earlier([lane]) && [Rank]=1),[Cost])
return
if(isblank([RANK]) || [RANK]<>1 , [Cost] -_min, blank())

 

Or

 

new column =
var _min = minx(filter(Table, [lane] =earlier([lane]) && [Rank]=1),[Cost])
return
if(isblank([RANK])  , [Cost] -_min, blank())

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @wek9294,

I think the above expressions are suitable for your requirement. If they meet your requirement, you can consider accepting these as a solution to help others who have similar requirements to find it more quickly.

If not, you can feel free to post here with detailed descriptions.

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@wek9294 , Try as a new column

 

new column =
var _min = minx(filter(Table, [lane] =earlier([lane]) && [Rank]=1),[Cost])
return
if(isblank([RANK]) || [RANK]<>1 , [Cost] -_min, blank())

 

Or

 

new column =
var _min = minx(filter(Table, [lane] =earlier([lane]) && [Rank]=1),[Cost])
return
if(isblank([RANK])  , [Cost] -_min, blank())

AlB
Super User
Super User

Hi @wek9294

 

Cost from non-rank =
VAR rank1Cost_ =
    CALCULATE (
        DISTINCT ( Table1[Cost] ),
        Table1[Rank] = 1,
        ALLEXCEPT ( Table1, Table1[Lane] )
    )
RETURN
    IF ( NOT Table1[Rank] IN { 1, BLANK () }, Table1[Cost] - rank1Cost_ )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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