Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Type | Location | PO# |
101 | 111 | 3501 |
101 | 112 | 3502 |
123 | 113 | 6503 |
123 | 111 | 6529 |
101 | 112 | 6522 |
101 | 113 | 6511 |
101 | 111 | 6544 |
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.
Solved! Go to Solution.
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:
Result:
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.
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
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
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:
Result:
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.
@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.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |