cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Filter Data using relationship

Hi, I have a table that contains collections target (for the months) and respective receipts information.

 

When I select “month”, I’d like to see the total target for the month in the card visual as well as the matrix visual.

 

However, when I select the “Det_TransDate” (which is basically the receipts date), I’d like to see the respective collections and balance outstanding across those dates (this should be dynamic) however, the target for the month should not change in the card visual or the matrix visual.

 

The goal is to see how much has been collected at month-end vs after month-end+10 days.

 

I have tried using a relationship however, the card visual seems to show correct data but the matrix visual does not filter data correctly for selected values in “Det_TransDate” slicer. It repeats the total receipts and total balance outstanding  value on all rows for some reason.

 

Is the desired result possible to achieve using one single table or having two same tables and having some sort of relationship between them?

 

Attached, is the sample BI file (pls. click on below link to download the file) with two same tables. Any help that could be provided would be greatly appreciated.

https://www.dropbox.com/s/jqos9w2yc7k8t4x/Coll.pbix?dl=0

 

-Deepak.

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Filter Data using relationship

Hi,

The first task should be to append data from both tables.  Then create a Calendar Table and build a relationship from the appended dataset to the Date column of the Calendar Table.  In the Calendar Table, write calculated column formulas to extract Year and Month.  Build any date related slicer from the Calendar Table.  The formula for the Target should be something like this

=CALCULATE(SUM(Data[Debtor Ageing Target Total]),ALLEXCEPT(Calendar,Calendar[Month]))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper I
Helper I

Re: Filter Data using relationship

Hi @Ashish_Mathur ,

 

Thank you for your  prompt reply.

The two tables that I am referring to (Collections & Receipts) are actually same table with identical data, only table name is different. I am not sure if my query can be resolved with one table itself, thus, two tables were provided.

 

The Target formula you gave works fine however in my report I also have a slicer "IntCo" with Yes and No entries. Sample pbix file I shared had only 'No' instances. So when a user clicks on No, Yes or Select All, the target number should change (in the card & matrix visual) accordingly, sorry this was not clear in my initial post. 

 

Also my target numbers are always as of month-end, collections happen following month onwards. For example if Target 'Month" is 6, collections for same will happen in Jul, Aug, Sept and so on.

 

Column 'DET_TRANSDATE' shows the actual receipt dates. So when a user selects on 'Month'=6, 'DET_TRANSDATE' will show dates from Jul, Aug & so on.

 

Objective is to allow users to select a 'Month' (for example 6), see their total target numbers and then also give them the option to see how much has been collected across 'DET_TRANSDATE' (for example Jul, Aug, Sep) against the total target numbers for the month. 

 

Thanks,

Deepak. 

Highlighted
Super User IV
Super User IV

Re: Filter Data using relationship

Hi,

I am confused in your first para itself - "The two tables that I am referring to (Collections & Receipts) are actually same table with identical data, only table name is different".  What was the need of creating a second Table?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper I
Helper I

Re: Filter Data using relationship

Hi @Ashish_Mathur 

 

I created two tables with the intention to achieve desired result by establishing relationship between the two tables so that I could fetch Target information from one table and receipts info from another table.

 

Reason: I want to see against my month-end target the collections that happen during the following month and the month after that. For example, if I select Target month 6 (June), I want to see total collections that happened in July (1st to 31st) and also I would like to see how much collections happened between July 1st to August 10th using the DET_TRANSDATE column (but the target number of 6 shold not change). Because we normally are able to collect 97-98% by August 10th.

 

If there is a way to achieve the desired result using one table am ok to use one table as well.

 

Thank you for all your assistance.

-Deepak.

Highlighted
Super User IV
Super User IV

Re: Filter Data using relationship

Hi,

Sorry but i will not be able to helo you with this.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Helper I
Helper I

Re: Filter Data using relationship

No worries @Ashish_Mathur 

 

Thank you for trying.

 

-Deepak.

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors