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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
383man
Regular Visitor

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 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?

 

breaker counts.png

1 ACCEPTED SOLUTION
v-jtian-msft
Community Support
Community Support

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

vjtianmsft_0-1716950276748.png

vjtianmsft_1-1716950294863.png

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

vjtianmsft_2-1716950320091.pngvjtianmsft_3-1716950332222.png

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:

vjtianmsft_4-1716950411215.png

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.

View solution in original post

4 REPLIES 4
383man
Regular Visitor

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.

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
Regular Visitor

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.table.png

v-jtian-msft
Community Support
Community Support

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

vjtianmsft_0-1716950276748.png

vjtianmsft_1-1716950294863.png

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

vjtianmsft_2-1716950320091.pngvjtianmsft_3-1716950332222.png

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:

vjtianmsft_4-1716950411215.png

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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