Filter a Column With Values of Another Column Using DAX Instead of Many to Many Relationship

-


So you want to filter a column in table A with values in a column in table B, but both contain duplicated data, the many to many can resolve this but I don't think this is your best approach so let's discuss how you can get around this.


  • why you may not want to use Power BI Many to Many even though you think you do:


Well, according to Power BI itself the many to many relationships create some ambiguity in your data and to your end-user as he/she won't know which column should he use to filter the data,  also according to Patrick from guy in a cube, it's not your best approach.


so here I'm going to introduce two approaches to solve this one that Patrick has already introduced 

and that one I've found using DAX and it is mind-blowing! 


  • First, let's look at the kind of ambiguity I'm talking about:


Here I've created some dummy data where table A and B contains city column
A with the sales of each city
Both columns contain the city name more than one
so it introduces many to many relationship

So here is what happened, it filtered the data right but the total
in the table which I used city from column B
is the same as the grand total of table A
which makes no sense

  • The First Approach: Creating a separate dim table:
You can create a dim table by taking the unique values of the table you want to filter by (or both tables) in a separate dimension table 

Here I've created a dimension table of only the unique names 
of the cities in table B
So I can filter table A with them in a one to many relationship


And of course, it introduces blanks that represent the values that are not in table B
so you can easily remove these blanks from the filter.

  • The second approach which is my favorite is using DAX:

Without any relationship!


And without any filter and it works just fine



Here's the measure used for that filtering:

sales for cities in table B = 

var A = VALUES('Table B'[city])

RETURN

CALCULATE(SUM('Table A'[sales]),FILTER('Table A','Table A'[city] IN A))


What this measure is doing is that it is taking the unique values of Table B city columns, stores them in variable A, then calculates the sales of table A cities filtered with these values. and it works just fine without even introducing a relationship.

Leave me a comment if you've tried or if you have a better approach.
Regards.



Comments

Post a Comment

Popular posts from this blog

Get The Distinct Count Of Values By Each Day Using DAX

-
Image
  We have a serious issue when it comes to calculating the distinct count of values based on dates, why? cause if you want to get the distinct count for each day, the usual distinct count measure will ignore the day-by-day calculating and will get the distinct count for the whole period.  so let's see how to overcome this. The solution is simple ... we will create a summarization by date and we will iterate through it calculating the distinct count This is the original measure that calculates the distinct count This is our new adjusted measure it simply defines a variable that holds a summarization of our table by date  and through this summarization , we will iterate and calculate the sum of the distinct count for each day Here we can see that for one day the two measures give the same value and it's reasonable  This is the tricky part that the new measure overcomes, for more days, the old measure gets the unique values for the whole bunch of days, not for each day....
Read more

Integrating Python in Power BI With An Example Of Data Wrangling & Data Visualization

-
Image
  With a mix of Power BI & Excel's versatility and coding experience, You can easily wrangle any kind of data and get the most out of it. Let's see how we can use python in two processes (Data Wrangling & Data Visualization) inside Power BI Make sure that anaconda and python3 installed on your pc. Our Data set The dataset I'm using for this article is a Kaggle dataset that I've recently worked on you can check it out here what we are going to do is to transform some of my work in this notebook to power bi. 2. Data wrangling The data wrangling process in power bi is based inside the query editor After loading your data click on the transform tab then run a python script then you will need to import your libraries and run your code for me, the dataset only needed to replace some values and to get the date and time  for more information check out my Kaggle notebook You will get this after running your code this is the new dataset after running your code  so all you...
Read more

Tips To Overcome Over plotting of Dense Scatter Plots.

-
Image
Here are some tricks to overcome overplotting of dense scatter plots Use a transparency factor: # Matplotlib scatter plot with an alpha value plt . scatter ( df [ X_COL ], df [ Y_COL ], alpha = 0.03 ) plt . xlabel ( X_COL ) plt . ylabel ( Y_COL ) plt . show () This will introduce some transparency to your visual and the less transparency the denser. You can make this even cooler with animated transparency: # Initialize plot and animation camera fig , ( ax1 , ax2 ) = plt . subplots ( 1 , 2 ) # Create 2 camera = Camera ( fig ) # Create a sequence of alpha values alpha_range = np . linspace ( 0.5 , 0 , 30 ) ** 3 # For each alpha value for alpha_value in alpha_range : # Plot "vanilia" plot for reference ax1 . scatter ( df [ X_COL ], df [ Y_COL ], color = 'black' ) # Plot scatter plot with the alpha value ax2 . scatter ( df [ X_COL ], df [ Y_COL ], alpha = alpha_value , color = 'black' ) # Take a ...
Read more