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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PowerBIFreak
Helper II
Helper II

Count the value start with certain value

TypeLocationPO#
1011113501
1011123502
1231136503
1231116529
1011126522
1011136511
1011116544

Table Name: "Return"

Hi Folks,

 

Based on the table above, I have to know how many PO# that start with 65 for each location with the type 101. I tried to create a measure and use the filter value but realized there is no wildcard. If I want to write a Dax for this, can I use the countif CountIF function?  Thank you.

2 ACCEPTED SOLUTIONS
v-angzheng-msft
Community Support
Community Support

 Hi, @PowerBIFreak 

Try to create a measure below:

_Return =
VAR text_PO =
    ( FORMAT ( SELECTEDVALUE ( 'Return'[PO#] ), "General Number" ) )
VAR _count =
    CALCULATE (
        COUNTROWS (
            FILTER ( 'Return', 'Return'[Type] = 101 && LEFT ( text_PO, 2 ) = "65" )
        )
    )
RETURN
    _count

It's worth noting that the Po field in my sample data is of numeric type.

If your PO field is text or you want to convert it to text, the text part can be removed from the above formula,

and replace it with ‘Return’[PO#] in LEFT function.

If your PO field type is the same as mine, it doesn't matter

Sample:

v-angzheng-msft_0-1619604168552.jpeg

Result:

v-angzheng-msft_1-1619604168553.jpeg

 

Please refer to the attachment below for details

 

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

 

 

Best Regards,
Community Support Team _ Zeon Zheng
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

Anonymous
Not applicable

i would add a column in query editor called "PO Flag" then simply do a 
if Type = 101 and Text.Start(PO#,2) = 65 then 1 else 0 
then you end up with a clean column with a 1 or 0 then create a measure that sums that result

Keeping measures simple makes the report faster and more efficient 

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

i would add a column in query editor called "PO Flag" then simply do a 
if Type = 101 and Text.Start(PO#,2) = 65 then 1 else 0 
then you end up with a clean column with a 1 or 0 then create a measure that sums that result

Keeping measures simple makes the report faster and more efficient 

 

v-angzheng-msft
Community Support
Community Support

 Hi, @PowerBIFreak 

Try to create a measure below:

_Return =
VAR text_PO =
    ( FORMAT ( SELECTEDVALUE ( 'Return'[PO#] ), "General Number" ) )
VAR _count =
    CALCULATE (
        COUNTROWS (
            FILTER ( 'Return', 'Return'[Type] = 101 && LEFT ( text_PO, 2 ) = "65" )
        )
    )
RETURN
    _count

It's worth noting that the Po field in my sample data is of numeric type.

If your PO field is text or you want to convert it to text, the text part can be removed from the above formula,

and replace it with ‘Return’[PO#] in LEFT function.

If your PO field type is the same as mine, it doesn't matter

Sample:

v-angzheng-msft_0-1619604168552.jpeg

Result:

v-angzheng-msft_1-1619604168553.jpeg

 

Please refer to the attachment below for details

 

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

 

 

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

Please disregard my previous reply,  I know why the other was not included,  it is because of the type is not 101. I'll try it out on my real report and let you know the results.  😁 thanks again.

Hello, 

Thanks for replying.  I opened the attachment and saw the result on the 'Return' column, why is it only index #5, 6, 7 has 1 on the 'Return' column? How about index # 3 & 4 since these 2 have PO# start with 65?

 

Thank you for your help.

amitchandak
Super User
Super User

@PowerBIFreak , Try a measure like

calculate(count(Return[PO#]), filter(Return, Return[Type] =101 && left([PO#],2) ="65"))

 

or

calculate(count(Return[PO#]), filter(Return, Return[Type] =101 && left([PO#] & "" ,2) ="65"))

Hello,

Thanks for replying. I created a new measure using the dax you provided but return a "Blank" reusult. Quick question: the part left([PO#], before the [PO#], it should be the table name, right? I tried both but still got the "Blank" result. What is the && for?

 

Thank you again.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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