How to create measures to calculate counts - Power BI desktop

[rrgeneral]

New to Power BI desktop. Using data connection to Analysis Server Models in PBI Desktop (May 2019).

I want to create 2 measures COUNT1 and COUNT2 based on data below. I tried using the quick measures and could not figure out. Any input? I see that certain features such as calculated column are disabled in Power BI desktop when using Analysis server models.

TIA!

Sample data

Category	Count
@A1	1
@a2	2
@a3	3
F123	15
F123	20

Count of categories starting with @character@ = COUNT1 = 6

Count of categories named F123 = COUNT2 = 35

[v-yingjl · Accepted solution]

Hi @rrgeneral ,

If you are using Live Connection mode to connect to Analysis Service, you could not use Containstring() function to create measure.

Try to create measures like this:

Count 1 =
SUMX (
    FILTER ( 'Sheet1', SEARCH ( "@", 'Sheet1'[Category],, 0 ) > 0 ),
    [Count]
)

Count 2 =
CALCULATE ( SUM ( Sheet1[Count] ), 'Sheet1'[Category] = "F123" )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, @rrgeneral 

Please try the below measures.

Count named F123 =

CALCULATE( SUM('Table'[Count]), 'Table'[Category] = "F123")

Count starting with @ =

CALCULATE( SUM('Table'[Count]), CONTAINSSTRING('Table'[Category], "@*"))

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[amitchandak]

@rrgeneral , You can have two measure to need to do dynamic segmentation

Calculate([Count], Filter(Table, Table[Category] = "F123"))

or

Calculate([Count], Filter(Table, Search("@",Table[Category],,0)>0))

Refer for Dynaminc segmentation

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
https://youtu.be/CuczXPj0N-k