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 ...
Need Help
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

Krishna1990_PBI's avatar
Krishna1990_PBI
New Member
3 years ago

I wanted to query Table2 Application Column based on Table1 Computer ID. So i used the folowing query but the measure value changes 

 

Should i go for calculated column to overcome this scenario 

 

_AppCount =
CALCULATE(
    DISTINCTCOUNT(Table1[ComputerID]),
    FILTER(
        ALL(Table2),
        CONTAINSSTRING(Table2[ApplicationName], "CrowdStrike")
    )
)
  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

     

    I'm struggling to understand why you need to involve Table1 in your measure.

    You have [ComputerID] in Table2 that you can use to count, and you have a ONE:MANY relationship between the two tables, so all of Table1 is already included in the expanded version of Table2.

     

    What am I missing here?

     

    Pete

    • Krishna1990_PBI's avatar
      Krishna1990_PBI
      New Member
      3 years ago

      Let's assume measure is created.  In first image, card created using table2 and Pie Chart created using Table1.

       

      When i made any selection within the pie chart, measure value also changing. It should not happen in my case.. How can we avoid that