is there a possibility yo apply a slicer to 2 separate tables?

[vika160]

I was searching for ability to have ability to apply a slider to 2 different tables.

I have 2 tables:

Application 

APPID	Type	Status	APPDate
1	personal	Aproved	jan 1,2022
2	personal	Aproved	jan 11,2022
3	personal	Aproved	jan 7,2022
4	personal	Closed	jan 8,2022
5	Corporate	Aproved	jan 8,2022
6	Corporate	Closed
7	personal	in progress
8	personal	in progress
9	Corporate	in progress
10	Corporate	Aproved	jan 9,2022

and Licence table:

LICID	APPID	Type	Status	APPDate
21	1	personal	Active	jan 1,2022
25	2	personal	Active	jan 11,2022
19	3	personal	Terminated	jan 7,2022
32	5	Corporate	Active	jan 8,2022
27	10	Corporate	Active	jan 9,2022

Not all approved Applications became  licences.

Licence can be later terminated

applications and Licences have type and Approval date the same.

I have  visuals and I would like to have a slicer to apply filter by type, AppData  and Status for both of them.

totals Apps

Total Lic

I am not sure if this even possible.

Any advises/hints/help would be greatly appreciated.

Thank you!!!

[v-angzheng-msft · Accepted solution]

Hi, @vika160 

Step 1: Create a calculation table (type table) to summarize the types
Step 2: Establish the relationship between the type table and the two fact tables
Step 3: Then create the type table based slicer.

Type Table = 
var _AppType=SUMMARIZE('Application',[Type])
var _LICType=SUMMARIZE('Licence table',[Type])
var _type=SUMMARIZE(
UNION(_AppType,_LICType),[Type])

return _type

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-angzheng-msft]

Hi,  @vika160 

I have  visuals and I would like to have a slicer to apply filter by type, AppData  and Status for both of them.

I'm not very clear on the expected result you want. Are you trying to create a multi-field slicer?

Could you please consider sharing more details about it and posting expected result so it is clear on what needs to be implemented?  And It would be great if there is a sample file without any sesentive information here.

It makes it easier to give you a solution.

1. Sample (dummy dataset) data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TheoC]

Hi @vika160 

You should be able to achieve what you're after by establishing a relationship between tables using APPID.

Hope this helps.

Theo

[vika160]

Yes, I tried.  It did not work the way I expected. May dig nto this more. thanks

[vika160]

One thing that I did not mentioned, as Application table has a lot payments yhe relations come as many-to-many

[TheoC]

@vika160 are you after a Date slider? Or are you after a Slicer?  Apologies, I may have misunderstood!  If you are after a Date slider, then you need to create a Date table (https://dax.guide/calendarauto/) and then create a link between the two tables to your date table.  It will be a one to many relationship from Date to your Fact tables.

Hope this helps 🙂

[vika160]

Hi @TheoC ,

 Thank you for your reply.

I have date table and  2 facts tables: Appplications and Licences.
Both facts tables are linked to the date table by Approval Date.

Every fact table has Type field: Corporate or Individual.

What I need is to apply Type selection for both visuals.

 Right now it applies only to visual that is based on the saem table the slicer is using Type field from.

I thought I would be able to achieve this by linking the fact tables by AppID but my Apps table has multuple rows for the same AppID as it also has all financial transcations attached. When I am trying to insert a relations - it gives me a warning:  The relationship has cardinality many-to-many ... ... The relations are not creating even if I was trying to mark "Create this relationship active checkbox.

 I am not sure how to achieve what I am looking for.

I hope this helps you understand better what I am looking for.

Thank you for your help.

[v-angzheng-msft · Accepted solution]

Hi, @vika160 

Step 1: Create a calculation table (type table) to summarize the types
Step 2: Establish the relationship between the type table and the two fact tables
Step 3: Then create the type table based slicer.

Type Table = 
var _AppType=SUMMARIZE('Application',[Type])
var _LICType=SUMMARIZE('Licence table',[Type])
var _type=SUMMARIZE(
UNION(_AppType,_LICType),[Type])

return _type

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vika160 · Accepted solution]

Thank you for your help. It is exactly what I was looking for. I will try your solution and report back. 

[vika160]

It is working! 
Thank you very much!