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 a column with a variety of license that each person has been assigned, as per column below.
I need a few measures so that I can report against certian license groupings, for example
1) Count rows that CONTAIN "Office 365" - it should count 2
2) Count rows that ONLY HAS "Office 365" - it should count 0
3) Count rows that CONTAIN "Office 365" and "Visio" - it should count 2
4) Count rows that CONTAIN "Office 365" Does NOT Contain "Word" - it should count 1
Licenses |
Office 365 + Word + Visio |
Visio + Project |
Office 365 + Visio |
Project + Visio |
Solved! Go to Solution.
@sharpedogs , 1 and 2 and are contradictory
You can use https://docs.microsoft.com/en-us/dax/containsstring-function-dax
with Switch true()
example -- Correct logic as per need. Order is also important
Switch (
CONTAINSSTRING('Table'[NAME],"Office 365") && CONTAINSSTRING('Table'[NAME],"Visio"), 2,
not(CONTAINSSTRING('Table'[NAME],"Office 365")) ,0 ,
CONTAINSSTRING('Table'[NAME],"Office 365") && not(CONTAINSSTRING('Table'[NAME],"Word")) , 1,
0)
HI @sharpedogs ,
You can try these measures
Only has Office365 =
VAR _texttobefound = "Office 365"
VAR _lengthoftext =
LEN ( _texttobefound )
RETURN
COUNTROWS (
FILTER (
'Table',
CONTAINSSTRING (
'Table'[Licenses],
_texttobefound
)
&& LEN ( 'Table'[Licenses] ) = _lengthoftext
)
)
Contains Office365 & Visio =
VAR _texttobefound1 = "Office 365"
VAR _texttobefound2 = "Visio"
RETURN
COUNTROWS (
FILTER (
'Table',
CONTAINSSTRING (
'Table'[Licenses],
_texttobefound1
)
&& CONTAINSSTRING (
'Table'[Licenses],
_texttobefound2
)
)
)
Contains Office365 & not Word =
VAR _texttobefound1 = "Office 365"
VAR _textnottobefound2 = "Word"
RETURN
COUNTROWS (
FILTER (
'Table',
CONTAINSSTRING (
'Table'[Licenses],
_texttobefound1
)
&& NOT CONTAINSSTRING (
'Table'[Licenses],
_textnottobefound2
)
)
)
Contains Office365 =
COUNTROWS (
FILTER (
'Table',
CONTAINSSTRING (
'Table'[Licenses],
"Office 365"
)
)
)
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
HI @sharpedogs ,
You can try these measures
Only has Office365 =
VAR _texttobefound = "Office 365"
VAR _lengthoftext =
LEN ( _texttobefound )
RETURN
COUNTROWS (
FILTER (
'Table',
CONTAINSSTRING (
'Table'[Licenses],
_texttobefound
)
&& LEN ( 'Table'[Licenses] ) = _lengthoftext
)
)
Contains Office365 & Visio =
VAR _texttobefound1 = "Office 365"
VAR _texttobefound2 = "Visio"
RETURN
COUNTROWS (
FILTER (
'Table',
CONTAINSSTRING (
'Table'[Licenses],
_texttobefound1
)
&& CONTAINSSTRING (
'Table'[Licenses],
_texttobefound2
)
)
)
Contains Office365 & not Word =
VAR _texttobefound1 = "Office 365"
VAR _textnottobefound2 = "Word"
RETURN
COUNTROWS (
FILTER (
'Table',
CONTAINSSTRING (
'Table'[Licenses],
_texttobefound1
)
&& NOT CONTAINSSTRING (
'Table'[Licenses],
_textnottobefound2
)
)
)
Contains Office365 =
COUNTROWS (
FILTER (
'Table',
CONTAINSSTRING (
'Table'[Licenses],
"Office 365"
)
)
)
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
@sharpedogs , 1 and 2 and are contradictory
You can use https://docs.microsoft.com/en-us/dax/containsstring-function-dax
with Switch true()
example -- Correct logic as per need. Order is also important
Switch (
CONTAINSSTRING('Table'[NAME],"Office 365") && CONTAINSSTRING('Table'[NAME],"Visio"), 2,
not(CONTAINSSTRING('Table'[NAME],"Office 365")) ,0 ,
CONTAINSSTRING('Table'[NAME],"Office 365") && not(CONTAINSSTRING('Table'[NAME],"Word")) , 1,
0)
Thanks.... that's exactly the function I needed...
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |