Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
giorgiokatr
Helper V
Helper V

count customers only in one category and not in other

i want in this table to count the customers that belong only to category a but not in other

result should be 3 (customer 2,3,5)

customercategory
1a
1b
2a
3a
4a
4b
5a

 

i used this function

onlycategorya:=CALCULATE (DISTINCTCOUNT (table[customer] );FILTER(VALUES(table[customer]);AND (CALCULATE ( HASONEVALUE (table[customer]));FIRSTNONBLANK(table[customer];0)="a")))

 

and it works but it is really slow in large dataset (20million rows)

any other ideas about optimizing this?

1 ACCEPTED SOLUTION

Hi @giorgiokatr

 

Sorry I missed the "a" in your post

 

Revised MEasure

 

MEASURE =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[customer],
            "MyCount", DISTINCTCOUNT ( 'Table'[category] ),
            "Belong to Category A", IF (
                CALCULATE ( COUNT ( 'Table'[category] ), 'Table'[category] = "A" )
                    > 0,
                "Yes",
                "No"
            )
        ),
        [MyCount] = 1
            && [Belong to Category A] = "Yes"
    )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

5 REPLIES 5
Zubair_Muhammad
Community Champion
Community Champion

HI @giorgiokatr

 

Try this MEASURE. See if its faster

 

MEASURE =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[customer],
            "MyCount", DISTINCTCOUNT ( 'Table'[category] )
        ),
        [MyCount] = 1
    )
)

Regards
Zubair

Please try my custom visuals

Photo.png


Regards
Zubair

Please try my custom visuals

thanks @Zubair_Muhammad its perfect but there are many categories in dataset so i would like to have the category a forx example as filter and not only the customers in one category (any one)

Hi @giorgiokatr

 

Sorry I missed the "a" in your post

 

Revised MEasure

 

MEASURE =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[customer],
            "MyCount", DISTINCTCOUNT ( 'Table'[category] ),
            "Belong to Category A", IF (
                CALCULATE ( COUNT ( 'Table'[category] ), 'Table'[category] = "A" )
                    > 0,
                "Yes",
                "No"
            )
        ),
        [MyCount] = 1
            && [Belong to Category A] = "Yes"
    )
)

Regards
Zubair

Please try my custom visuals

thanks @Zubair_Muhammad!!

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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