Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Excel to power bi

Have a calculation that i need to input to power bi.  Any help is greatly appericated.

 

Excel expression...

=COUNTIFS(Usage[Treatment Variance],"<75%",Usage[Pump is on Mirador and fully operational],"")+COUNTIFS(Usage[Treatment Variance],"<75%",Usage[Pump is on Mirador and fully operational],"No")

  • MahyarTF's avatar
    MahyarTF
    4 years ago

    Hi,

    Yo could try below measure :

    _ExcelFormula = CALCULATE(DISTINCTCOUNT(Sheet175[Treatment Variance]),
                              FILTER(Sheet175,
                                     Sheet175[Treatment Variance] < 0.75 &&
                                     or(Sheet175[Pump is on Mirador and fully operational] = "",
                                        Sheet175[Pump is on Mirador and fully operational] = "No")
                                    )
                            )

     

    Appreciate your Kudos

2 Replies

  • MahyarTF's avatar
    MahyarTF
    Icon for Memorable Member rankMemorable Member
    4 years ago

    Hi,

    Yo could try below measure :

    _ExcelFormula = CALCULATE(DISTINCTCOUNT(Sheet175[Treatment Variance]),
                              FILTER(Sheet175,
                                     Sheet175[Treatment Variance] < 0.75 &&
                                     or(Sheet175[Pump is on Mirador and fully operational] = "",
                                        Sheet175[Pump is on Mirador and fully operational] = "No")
                                    )
                            )

     

    Appreciate your Kudos

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, (I just saw that MahyarTF posted and that look like it would work better!) 
    Anyhow, here is how I started tackling the problem.

    I think I would need some sample data in order to get this right. 

    I think it would look something like:

    Counting = 
    Calculate(COUNTA('Usage'[Treatment Variance]),
    FILTER('Usage', 'Usage'[Treatment Variance] < 75% &&
    Usage[Pump is on Mirador and fully operational] = "" ||
    'Usage'[Treatment Variance] < 75% &&
    Usage[Pump is on Mirador and fully operational] = "No")

    However, this is just a general concept, I think you could also refer to this: https://community.powerbi.com/t5/Desktop/Calculate-with-OR-AND-NOT-in-filter/m-p/60692