replaces the values of same columns on conditions from same and other column

[kumsha1]

Hi,

Can someone help me with the DAX for below. TIA.

Current format

Required format

If Att=Running Hours and Values=0 then (Values=0 where Att=Throughput)

[TheoC]

Hi @kumsha1 

What is the output you hope to see from the calculation?

Thanks,
Theo

[kumsha1]

Hi @TheoC 

If Att=Running Hours and Values=0 then i want to make the Throughput values 0 as below

Att	Values
Running Hours	0
Throughput	0.00

[kumsha1]

There could be other Att in the table, so i just want to make the values 0 where Att=Throughput

[TheoC]

Hi @kumsha1 

You can use a calculated column like this:

_Value = 

VAR _1 = "Running Hours"
VAR _2 = "Throughput"
VAR _3 = SWITCH ( TRUE () , 'Table'[Att] = _1 && 'Table'[Values] = 0 , 'Table'[Values] )
VAR _4 = SWITCH ( TRUE () , _3 = 0 && 'Table'[Att] = _2 , 0 , 'Table'[Values] )

RETURN

_4

Output is like below:

PBIX also attached for further help 🙂

In the example above, I hadded a few other "Att" items just to show you that the calculated column only picks up the Running Hours that are 0 and then creates the Throughput to be 0 as well, as required.

All the best!

Theo 

[kumsha1]

Hi @TheoC , this seems to be working for distinct individual Att values but not for duplicate values as mentioned below. TIA

[TheoC]

@kumsha1 can you please provide all of the data in the exact state that it is in that you are working with?  It seems as though there is missing data and each attempt at providing a solution brings new information. To ensure that @Anonymous and I can assist you, can you please provide sample data that matches the exact data in your Power BI file.  @Anonymous has provided some really great solutions and I am a bit surprised that these are not working for you, hence the need to see the sample data or alternatively get a copy of your PBIX file.  

Thank you in advance for your understanding.

Theo

[Anonymous]

Hi @kumsha1 ,

I think TheoC's code will let all "Throughput" value return 0 if there is one "Running Hours" value equal to 0. So you will get result as the above screenshot. Accroding to your screenshot, I know there should be the same number of "Throughput" and "Running Hours". Is there a one-to-one correspondence between them? So you want one of "Throughput" returns 0 and other three return the original values. If they are one-to-one, then there should be a column like [Index] to determind this.

Table should look like as below.

If there is not a column like [Index] in your table, you can add it by yourself in Power Query Editor.

For reference:

Create Row Number for Each Group in Power BI using Power Query

Try this code.

New Value =
VAR _Value_Att =
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER (
            'Table',
            'Table'[Index] = EARLIER ( 'Table'[Index] )
                && 'Table'[Attribute] = "Running Hours"
        )
    )
RETURN
    SWITCH (
        'Table'[Attribute],
        "Throughput", IF ( _Value_Att = 0, 0, 'Table'[Values] ),
        'Table'[Values]
    )

Result is as below.

Best Regards,
Rico Zhou

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

[kumsha1]

Hi @Anonymous , thanks for your reply.

Below is my data. Attributes can be showed together or individually on different tables and they also have table level filters applied. In either case i  want to show Througput value as 0 when the Attribute=Running Hours and Values=0. I am quite not sure if ReportElement can be used to make 1-1 relationship..TIA

[Anonymous]

Hi @kumsha1 ,

I think you can try this code to create a measure to achieve your goal.

New Value = 
VAR _Value =
    CALCULATE ( SUM ( 'Table'[Values] ) )
VAR _Value_Att =
    CALCULATE (
        SUM ( 'Table'[Values] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ReportElement] ),
            'Table'[Attribute] = "Running Hours"
        )
    )
RETURN
    SWITCH (
        MAX ( 'Table'[Attribute] ),
        "Throughput", IF ( _Value_Att = 0, 0, _Value ),
        _Value
    )

Result is as below.

Best Regards,
Rico Zhou

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

[kumsha1]

Thanks again @Anonymous , above solution is not working for me and i think it works only if we have two attributes since MAX was used in RETURN statement, there could be many other attributes with different table filters. I really need a solution that works in any scenario. I am also trying if we can do this by another duplicate table but not sure if it works, if you come across any other solutions pls let me know here. cheers