Forum Discussion

Krishna1990_PBI's avatar
Krishna1990_PBI
New Member
3 years ago

Need help on Creating a measure based on another table's Column

I have two tables. I want to create a measure from first table by using second table column.

Created a following measure but measure value changes when I click the visuals though I used FILTER with ALL function.

 Table 1 & Table 2 (One to Many relationship created)

Computer ID

100

101

102

104

103

 

Computer ID

ApplicationName

100

CrowdStrike

101

CrowdStrike

102`

Symantec

 

AppCount = CALCULATE(DISTINCTCOUNT(Table1[Computer ID]),FILTER(All(Table1),Table1[Computer ID]),FILTER(All(Table2),CONTAINSSTRING(Table2[ApplicationName],"CrowdStrike")))

Need Help

6 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi Krishna1990_PBI ,

     

    I may be oversimplifying this, but it looks like you just want to find out the number of computers that have an [ApplicationName] LIKE "CrowdStrike".

    If so, then the following should work fine:

    _AppCount =
CALCULATE(
    DISTINCTCOUNT(Table2[ComputerID]),
    CONTAINSSTRING(Table2[ApplicationName], "CrowdStrike")
)

     

    As you have a Table1 ONE : MANY Table2 relationship, this should calculate correctly for different selections of Table1.

     

    If you don't actually need the 'LIKE' comparison, but an 'EQUALS' comparison, then it's even simpler:

    _AppCount =
CALCULATE(
    DISTINCTCOUNT(Table2[ComputerID]),
    Table2[ApplicationName] = "CrowdStrike"
)

     

    Pete

    • Krishna1990_PBI's avatar
      Krishna1990_PBI
      New Member
      3 years ago

      Hi Pete.

       

      Thanks for the reply. 

       

      I wanted to keep that measure value remain unchanged when I click any visuals from the board. Curently the value is changing.

       

       

      Regards

      Krishna G

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        Ok, so the following should work:

        _AppCount =
CALCULATE(
    DISTINCTCOUNT(Table2[ComputerID]),
    FILTER(
        ALL(Table2),
        CONTAINSSTRING(Table2[ApplicationName], "CrowdStrike")
    )
)

// OR

_AppCount =
CALCULATE(
    DISTINCTCOUNT(Table2[ComputerID]),
    FILTER(
        ALL(Table2),
        Table2[ApplicationName] = "CrowdStrike"
    )
)

         

        Pete