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

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
RafDelgado
Helper II
Helper II

Measure to filter two seperate tables

Hoping someone can help, I'm working on a report which has two tables which cannot be joined by relationship. The first is a list of server sizes with CPU cores and memory. The second table is also list of servers with a number of cores and memory (which will have a slicer with just the server names to filter single value).

 

2017-01-12_19-58-56.png

 

 

2017-01-12_19-59-31.png

 

What I'd like to be able to do is have a filter to strip out the server sizes from the first table which are not relevant. So for example,

 

if the cores from table 2 is 8 then ,

bring back =>8, then filter those with have memory of =>2

 

 

Can anyone advise of a way to do this? I'm struggle to get the meaure to make it work with it not being in a relationship.

 

Thanks in advanced,

 

Kind Regards,

 

Raf

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

Hi @RafDelgado,

In your second table, create a measure using the following formula.

s = IF(HASONEVALUE(t2[CoreCount]),VALUES(t2[CoreCount]),BLANK())

In  your first table, create a measure using the following formula.
checkresult = IF(MAX(t1[CPU cores])>=[s]&&MAX(t1[Memory:GB])>=2,1,0)

Create a table visual shown in the following screenshot. And create a slicer using CoreCount field of second table.
1.PNG2.PNG

Set the value of the slicer to 8, then set value of checkresult measure to 1 in visual level filter, you will get expected result.
3.PNG

 


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

Hi @RafDelgado,

In your second table, create a measure using the following formula.

s = IF(HASONEVALUE(t2[CoreCount]),VALUES(t2[CoreCount]),BLANK())

In  your first table, create a measure using the following formula.
checkresult = IF(MAX(t1[CPU cores])>=[s]&&MAX(t1[Memory:GB])>=2,1,0)

Create a table visual shown in the following screenshot. And create a slicer using CoreCount field of second table.
1.PNG2.PNG

Set the value of the slicer to 8, then set value of checkresult measure to 1 in visual level filter, you will get expected result.
3.PNG

 


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft,

 

Thank you for replying, this is perfect to get me started, I ended up making the changes in the query editor via a column merge but this is so much better.

 

Thanks again Smiley Very Happy,

 

Raf

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.

October NL Carousel

Fabric Community Update - October 2024

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