Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table that has a list of URLs and I would like to create a count of all of the ones that end in .com, .org, .gov etc. How can I write a formula that will do this?
Solved! Go to Solution.
Hi @camruny,
Adding to other post, you can simply create a measure using CALCULATE and COUNTROWS functions to create a count of all of the ones that end in .com, .org, .gov . Here is an example for your reference.
Thanks,
Lydia Zhang
Hi @camruny,
Adding to other post, you can simply create a measure using CALCULATE and COUNTROWS functions to create a count of all of the ones that end in .com, .org, .gov . Here is an example for your reference.
Thanks,
Lydia Zhang
Thank you! This is exactly what I was looking for!
If you use the SEARCH function, you can take advantage of wildcards. Since the criteria is static, I'd probably make a new column in the data view that determines if the ending of the url matches your pattern. For example:
You can then use that column in your measures to get a count of urls that match. If you multiply the whole column formula above by 1, you'll end up with 1s for True and 0s for False, making any math you do on the column easier and more intuitive. Then you can use:
MatchCount = SUM(Table1[Matches])
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |