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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
carlochecchia
Helper I
Helper I

Filter data from other tables

Hi all,

I was wondering ig you could help me with the following issue:
I currently have 3 datasets:
a. Source

 

IDWeight
10011
10022
10033

 


b. abc

 

ID#Answer
10011Blue
10012Beans
10021Red
10022Peppers
10031Blue
10032Beans


c. DEF

IDTypeSEG
1001GenderMale
1002GenderMale
1003GenderFemale
1001CountryUSA
1002CountryCanada
1003CountryUsa

 


I can create a relationship between 'Source & def' and 'Source & abc'. (One a one to many relationship.)
I could have a relationship between 'Segment & abc' but it would be a many to many relationship and I am trying to avoid this type of relationship.

Here is the issue.
I am trying to run calculations, applying a filter from 'Segments' on the 'abc' dataframe, using a sum from the Source table.
Since, it doesnt have a relationship (cde and abc), it cant calculate correctly

EXAMPLE.JPG

 

 

 

 

 

 

 

 

How can i properly calculate this?

 

Thanks in advance for taking the time to look into this matter

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, @carlochecchia 

 

I am not sure how the below measure will influence your real case, but if your objective is to achieve the result that is shown in your picture, please try the below. 

 

measurefix =
CALCULATE (
SUM ( Source[Weight] ),
CROSSFILTER ( Source[ID], Questions[ID], BOTH ),
CROSSFILTER ( Source[ID], Segment[ID], BOTH )
)

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
carlochecchia
Helper I
Helper I

Thank you so much!

Could you also assist me on how to calculate the percentage of the column total?
Looking to achieve this result

Answer Female Male Total
blue 100% 33% 67%
Red -- 66% 33%
total 100% 100% 100%

Hi, 

Please try the below.

 

measure =
CALCULATE (
SUM ( Source[Weight] ),
CROSSFILTER ( Source[ID], Questions[ID], BOTH ),
CROSSFILTER ( Source[ID], Segment[ID], BOTH )
)
 
percentage =
DIVIDE( [measure], CALCULATE([measure], ALLSELECTED( Questions)))
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Jihwan_Kim
Super User
Super User

Hi, @carlochecchia 

 

I am not sure how the below measure will influence your real case, but if your objective is to achieve the result that is shown in your picture, please try the below. 

 

measurefix =
CALCULATE (
SUM ( Source[Weight] ),
CROSSFILTER ( Source[ID], Questions[ID], BOTH ),
CROSSFILTER ( Source[ID], Segment[ID], BOTH )
)

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Thank you once again!
I have one last question, if possible.
Still taking in account the same scenario, but adding a 'Date' Column to the Source dataframe:

 

ID Weight Date
1001   1   01/01/21

1002   2   01/02/21

1003   3   01/03/21

 

Would it be possibe to calculate the accumulated weight by month, so it gives the following result:

 

--01/01/2021 01/02/2021 01/03/2021 total
blue 1             1                 4                    4
red   -             2                 2                    2
total 1             3                6                      6

Hi, @carlochecchia 

Please try the below.

 

Cumulate Weight =
CALCULATE (
SUM ( Source[Weight] ),
CROSSFILTER ( Questions[ID], Source[ID], BOTH ),
CROSSFILTER ( Segment[ID], Source[ID], BOTH ),
FILTER ( ALLSELECTED ( Source ), Source[Date] <= MAX ( Source[Date] ) )
)

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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