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
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")))
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
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
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
Proud to be a Datanaut!
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
58 | |
53 | |
26 | |
16 | |
9 |