Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have created a measure that tells me if a "cell" contains a specific partial text. How can I count/sum that measure?
Ex:
The table looks something like this:
And the measure looks for "Direct - " using the following formula:
Solved! Go to Solution.
Hi @DemingPDCA ,
You could modify the measure as follows.
Count Direct Gross Margin Impact =
COUNTROWS (
FILTER (
'Division Quality Project Tracker',
CONTAINSSTRING (
'Division Quality Project Tracker'[Gross Margin Impact],
"Direct - "
)
)
) + 0
You can see that it has a total of 3, and if you want each row to return a total, you can add ALLSELECTED function.
Count Direct Gross Margin Impact =
COUNTROWS (
FILTER (
ALLSELECTED ( 'Division Quality Project Tracker' ),
CONTAINSSTRING (
'Division Quality Project Tracker'[Gross Margin Impact],
"Direct - "
)
)
)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @DemingPDCA ,
You could modify the measure as follows.
Count Direct Gross Margin Impact =
COUNTROWS (
FILTER (
'Division Quality Project Tracker',
CONTAINSSTRING (
'Division Quality Project Tracker'[Gross Margin Impact],
"Direct - "
)
)
) + 0
You can see that it has a total of 3, and if you want each row to return a total, you can add ALLSELECTED function.
Count Direct Gross Margin Impact =
COUNTROWS (
FILTER (
ALLSELECTED ( 'Division Quality Project Tracker' ),
CONTAINSSTRING (
'Division Quality Project Tracker'[Gross Margin Impact],
"Direct - "
)
)
)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HI @DemingPDCA
Please try the following:
COunt =
SUMX(
VALUES(Table[Column in your Fact table for granularity definition]),
var __check = CONTAINSSTRING(SELECTEDVALUE('Division Quality Project Tracker'[Gross Margin Impact]), "Direct - ")
Return
IF(__check = TRUE(),1,0)
)
Based on your request its hard to say what to put into the VALUES() function. In the end my formula iterates through a list of items which and executes tzhe check. if it is true the the result is 1 else it is 0. In the end all 1 are summed up.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |