Forum Discussion

satish_pb's avatar
satish_pb
New Member
7 years ago
Solved

Calculate sum using text wild characters.

I am trying to calulate a measure to sum the quantity of incidents where the text contains VFF.

 

The query i am using 

New VFF = calculate (sum(Query1[QTY]),Query1[Categorized_Disposition] = "*VFF*")
 
I am getting 0 hits. But i know if have text starting with VFF.
Need Help
  • RobbeVL's avatar
    RobbeVL
    7 years ago

    Hi There,

    DAX doesnt use Operators like SQL, it only recognizes the * as text.

    try this:

    New VFF = calculate (sum(Query1[QTY]), LEFT(Query1[Categorized_Disposition],3) = "VFF"



  • TeigeGao's avatar
    TeigeGao
    7 years ago

    Hi satish_pb ,

    We can use the FIND() function to get the list of quantity of incidents where the text contains VFF, please refer to the following DAX query:

    Measure =
SUMX (
    'Table',
    IF (
        FIND ( "VFF", 'Table'[Categorized_Disposition], 1, BLANK () ) = BLANK (),
        0,
        'Table'[QTY]
    )
)

    The result will like below: 

    Best Regards,

    Teige

2 Replies

  • RobbeVL's avatar
    RobbeVL
    Impactful Individual
    7 years ago

    Hi There,

    DAX doesnt use Operators like SQL, it only recognizes the * as text.

    try this:

    New VFF = calculate (sum(Query1[QTY]), LEFT(Query1[Categorized_Disposition],3) = "VFF"



  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    Hi satish_pb ,

    We can use the FIND() function to get the list of quantity of incidents where the text contains VFF, please refer to the following DAX query:

    Measure =
SUMX (
    'Table',
    IF (
        FIND ( "VFF", 'Table'[Categorized_Disposition], 1, BLANK () ) = BLANK (),
        0,
        'Table'[QTY]
    )
)

    The result will like below: 

    Best Regards,

    Teige