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
Anonymous
Not applicable

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
Anonymous
Not applicable

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. 

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/
Anonymous
Not applicable

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.

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/
Anonymous
Not applicable

No worries @Ashish_Mathur 

 

Thank you for trying.

 

-Deepak.

Ashish_Mathur
Super User
Super User

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/

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.