MEASURE TO FILTER?

[383man]

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 that have lines downstream feeding distribution customers, and we have the counts for those, great. then we have devices downstream of those same breakers feeding distrution customers as well (just less of them as they are downstream of the breakers). so say the BLDR41BKR (Breaker) feeds 2031 customers, and the BLDR41R270 (recloser) feeds 327 customers, and the BLDR41R306 (recloser) feeds 117 customers, and the BLDR41R420 (recloser) feeds 180 customers, my total count is saying we have 2,655 customers, but truly we only have 2031 as all of the reclosers with lesser numbers are the same customers as the breaker. hope i explained that well enough. there are cases however where there isn't a breaker listed, just recloser, fuse, etc... is there a way to do a measure (or something like that) that prioritizes an item so basically if a given feeder has a breaker, only count the breaker, if not, count out everything?

[383man]

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.

[v-jtian-msft]

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.

[383man]

that is super clever and would work, if i could remove duplicates, however, i need the list to show duplicates as there is also a column called "Risk Areas" which is the main filter criteria. and there may be multiple risk areas with the same device, and also multiple "action types" that have multipe risk areas on them... it's kind of duplicating to the max, but when I filter the data down to just show "action type" of "PSPS Action" in a table, and a total sum card at the top filtered to the same, but that sum card is double counting based on the previous ask. i attached the full screen grab of the table to show it's complexity.

[v-jtian-msft · Accepted solution]

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.