Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
12 | |
12 | |
11 | |
8 |
User | Count |
---|---|
42 | |
25 | |
14 | |
14 | |
12 |