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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ASI80486
Frequent Visitor

Iterating over rows based on filter to write multiple if else statement and perform calculations

ankita80486_0-1658854521764.png

In the above table:

1. Need to FILTER  by ([Month Name not equal]="Y"  and based on Asset Number) , since there are many other assets and this has to be done for each asset, ([Month Name not equal]="Y" will filter be Feb March April

ankita80486_1-1658854649413.png

 

2. I need to write conditions and then update the next value for New DT

   IF[New DT] >= [Hrs Bw St and End Date] then [Hrs Bw St and End Date] else  [New DT] (1779.55>=520.04)  result of new column 520.04

  Update [New DT for next month(March)= [New DT]- [Hrs Bw St and End Date] New DT (March)-->1259.51

  IF[New DT] >= [Hrs Bw St and End Date] then [Hrs Bw St and End Date] else  [New DT] (1259.51>=744)  result of new column 744

  [New DT for next month(April) = [New DT] - [Hrs Bw St and End Date] (April)--> 515.51

  IF[New DT] >= [Hrs Bw St and End Date] then [Hrs Bw St and End Date] else  [New DT] (515.51<=516.5) result of new colum 515.51

 

MonthNew column for DT
Feb520.04
March744
April515.51

 

Can someone help please??

1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@ASI80486 create this calculated column:

 

Final Column = 
VAR _asset = 'asset_downtime (3)'[Asset Number]
VAR _time = 'asset_downtime (3)'[Start Date]
VAR _total = SUMX(FILTER('asset_downtime (3)', 'asset_downtime (3)'[Asset Number] = _asset && 'asset_downtime (3)'[Month Name not equal] = "Y"),'asset_downtime (3)'[New DT]) 
VAR _substract =
    SUMX(
        FILTER(
            'asset_downtime (3)',
            'asset_downtime (3)'[Asset Number] = _asset 
                && 'asset_downtime (3)'[Month Name not equal] = "Y" 
                    && 'asset_downtime (3)'[Start Date] < _time
        ),
        'asset_downtime (3)'[Hrs Bw St and End Date]
    )
VAR _result = 
    SWITCH(
        TRUE(),
        'asset_downtime (3)'[Month Name not equal] = "N", 'asset_downtime (3)'[New DT],
        'asset_downtime (3)'[Hrs Bw St and End Date] <= 'asset_downtime (3)'[New DT], 'asset_downtime (3)'[Hrs Bw St and End Date],
        _total - _substract > 'asset_downtime (3)'[Hrs Bw St and End Date], 'asset_downtime (3)'[Hrs Bw St and End Date],
        _total - _substract
    )
RETURN
    MAX(_result, 0 )

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

6 REPLIES 6
SpartaBI
Community Champion
Community Champion

@ASI80486 create this calculated column:

 

Final Column = 
VAR _asset = 'asset_downtime (3)'[Asset Number]
VAR _time = 'asset_downtime (3)'[Start Date]
VAR _total = SUMX(FILTER('asset_downtime (3)', 'asset_downtime (3)'[Asset Number] = _asset && 'asset_downtime (3)'[Month Name not equal] = "Y"),'asset_downtime (3)'[New DT]) 
VAR _substract =
    SUMX(
        FILTER(
            'asset_downtime (3)',
            'asset_downtime (3)'[Asset Number] = _asset 
                && 'asset_downtime (3)'[Month Name not equal] = "Y" 
                    && 'asset_downtime (3)'[Start Date] < _time
        ),
        'asset_downtime (3)'[Hrs Bw St and End Date]
    )
VAR _result = 
    SWITCH(
        TRUE(),
        'asset_downtime (3)'[Month Name not equal] = "N", 'asset_downtime (3)'[New DT],
        'asset_downtime (3)'[Hrs Bw St and End Date] <= 'asset_downtime (3)'[New DT], 'asset_downtime (3)'[Hrs Bw St and End Date],
        _total - _substract > 'asset_downtime (3)'[Hrs Bw St and End Date], 'asset_downtime (3)'[Hrs Bw St and End Date],
        _total - _substract
    )
RETURN
    MAX(_result, 0 )

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Thank you @SpartaBIfor your quick response and solution. You helped me solve this issue.Thanks so much for your time.

@ASI80486 my pleasure 🙂

otravers
Community Champion
Community Champion

In Power Query, go to Add Column > Add conditional column. There you'll have a user interface that produces a series of if (else) statements. Start from there, and if you can't do exactly what you want from the UI, you can always tweak the generated code manually from the Advanced Editor.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

here the issue is to update the New DT column with new value and then use that updated new DT value in the next IF statement to calculate the next value in the new column

Oh, I see, it's on a row-by-row basis. I don't think there's a solution using purely the Power Query UI, I think you'd have to add an index column and write some M code. It's probably something I'd solve in DAX though. Sorry I don't have time to flesh it out further, maybe other people will chime in.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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