Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Krishna1990_PBI
New Member

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")))

6 REPLIES 6
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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")
    )
)

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

Krishna1990_PBI_0-1689262734283.png

 

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

Krishna1990_PBI_1-1689262734295.png

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.