Forum Discussion

383man's avatar
383man
Regular Visitor
2 years ago
Solved

MEASURE TO FILTER?

So i have a list of data, lots of it (filtered to just one type to see what i'm trying to do) and i'm trying to do an accurate customer count. however, it's doublecounting. so, there are breakers tha...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,383man 
    I am glad to help you.

    According to your description, you want to calculate the number of users, if there is a record with the suffix “BKR”, then directly return its “CUST COUNTS” value.

    But sometimes there are no breakers, if there are no breakers, then calculate all the non-repeated numbers and add them up 327+117+180+....

    If my understanding is correct, you can refer to my following test

    First I recreated two tables and de-duplicated the duplicate data in them
    like this：

    Then I created three measures

    Total CUST COUNTS BKR = 
CALCULATE(SUM('Table_2'[CUST COUNTS]),FILTER('Table_2',RIGHT('Table_2'[NAME CORRECTED], 3) = "BKR"&&'Table_2'[Feeder]=MAX('Table_2'[Feeder])
)
)

    Total CUST COUNTS NotBKR = 
CALCULATE(SUM('Table_2'[CUST COUNTS]),FILTER('Table_2',RIGHT('Table_2'[NAME CORRECTED], 3) <>"BKR"&&'Table_2'[Feeder]=MAX('Table_2'[Feeder])
)
)

    M_result = 
IF(
    COUNTROWS(
        CALCULATETABLE('Table_2',
            FILTER('Table_2', RIGHT('Table_2'[NAME CORRECTED], 3) = "BKR")
        )
        
    ) > 0,
    [Total CUST COUNTS BKR],
    [Total CUST COUNTS NotBKR]
)

    The final test result is as follows:

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

    Best Regards,

    Carson Jian,

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

383man's avatar
383man
Regular Visitor
2 years ago

I figured it out, kind of convoluted, but i did 3 seperate measures, one for each "action type", then i had to create a unique field since none where, so i concatenated "feeder" and "risk zone", and did counts based on each action type, didn't explain it well here, but it works, thanks for the help, v-jtian-msft you gave me the idea i needed to seperate them out! thanks again.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,383man .
I am very happy to be able to help you with your problem. If you have any other questions about Power BI, you are welcome to share them on the forums as it will help more users with similar issues!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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