topic Re: Need to show a table visual which shows difference of two identical tables of some components in DAX Commands and Tips

Need to show a table visual which shows difference of two identical tables of some components

Anonymous — Sat, 12 Mar 2022 06:34:23 GMT

Hi Microsoft,

I have an exact sceneio of this link https://youtu.be/hu_fOtZDJwU

I could able to create duplicate table and established an inactive relationship and written a DAX code like below

Comparison _abc = CALCULATE([ABC],ALL('Date'),USERELATIONSHIP(Date_Dup[CYYMNN],Facttable[CYYMNN]))

By establishing inactive relationship in data model and writting an above DAX upon that, I could able to achieve the requirement.

But, now, my team does not want touch the existing data moadel and do not want to establish an inactive relationship in the dataset because it is in live connection.

Whatever , I need to do, I have to use only DAX/create only measures . So I got struck , how can I replace Userelationship function from above DAX code with something else inorder to achieve my scenerio without touching data model.

PLease guide.

Thank You

Re: Need to show a table visual which shows difference of two identical tables of some components

tamerj1 — Sat, 12 Mar 2022 07:52:27 GMT

hi Anonymous

are you even allowed to create the duplicate calendar table or this is also not allowed?

Re: Need to show a table visual which shows difference of two identical tables of some components

tamerj1 — Sat, 12 Mar 2022 08:06:01 GMT

If yes you can do the following

Comparison _abc = CALCULATE ( [ABC], INTERSECT ( ALL ( 'Date'[Year], 'Date'[Month Name] ), SUMMARIZE ( Date_Dup, Date_Dup[Year], Date_Dup[Month] ) ) )

toy might need the change column names or add/remove coulmns base on with coulmns are used for slicing.

Re: Need to show a table visual which shows difference of two identical tables of some components

Anonymous — Sun, 13 Mar 2022 02:46:57 GMT

Hi Tamerji1,

Thank You for replying!

I have 5 duplicate tables , not only Date table. For eg.,
1. Date_Dup-- Have columns (Month, year, CYYMNN)

2. HSB_Dup--Have columns(HSB_ID)

3. PAM_Dup-- Have columns(PAM_ID)

4. RP_Dup-- Have columns(PA_ID)

5. SU_Dup-- Have columns(STO_ID)

All those columns , I will be passing in a slicer for comparing.

How to pass all of them now?

Re: Need to show a table visual which shows difference of two identical tables of some components

Anonymous — Sun, 13 Mar 2022 02:56:49 GMT

Hi Tamerji1,

I need to show following output

GDP	abc	comparison_abc	Difference between abc and comparison_abc
1	1000	8000	-7000
2	3000	300	2700
3	3500	4000	-500
4	500	1000	-500

Here abc and comparison_abc are identical measures(same to same )

Re: Need to show a table visual which shows difference of two identical tables of some components

tamerj1 — Sun, 13 Mar 2022 03:50:13 GMT

Hi Anonymous
Can you please provide some screenshots of your data model and indicate which relationships you wish to create but you're not allowed to.

Re: Need to show a table visual which shows difference of two identical tables of some components

Anonymous — Sun, 13 Mar 2022 04:42:27 GMT

Hi Tamerji1,

Please find the screenshots attached in a document. Please let me know if you need any other details as well.

Re: Need to show a table visual which shows difference of two identical tables of some components

tamerj1 — Sun, 13 Mar 2022 06:33:59 GMT

Hi Anonymous

you can the same approach to replace USERELATIONSHIP for all measures.

[Filtered Measure] := CALCULATE ( <target_measure>, INTERSECT ( ALL ( <target_granularity_column_1>, <target_granularity_column_2> ), SUMMARIZE ( <lookup_table> <lookup_granularity_column_1> <lookup_granularity_column_2> ) ) )

However, this really depends on how your report is.
if you wish you can share a sample no sensitive data file with one to cases I'll help you out with them

Re: Need to show a table visual which shows difference of two identical tables of some components

Anonymous — Sun, 13 Mar 2022 06:43:33 GMT

Hi tamerj1,

Is there any way , to connect with you. So that I can share my screen and explain you.

Re: Need to show a table visual which shows difference of two identical tables of some components

tamerj1 — Sun, 13 Mar 2022 07:02:33 GMT

Hi Anonymous

Would be possible only on next Saturday.

Re: Need to show a table visual which shows difference of two identical tables of some components

Anonymous — Sun, 13 Mar 2022 07:34:21 GMT

Hi Tamerj1,

Sure, Thank you 🙂

Re: Need to show a table visual which shows difference of two identical tables of some components

Anonymous — Thu, 17 Mar 2022 09:42:33 GMT

Hi Tamerj1,

Hope we are connecting on Saturday. Is there a way/link and Time so that I can be ready with files .

Re: Need to show a table visual which shows difference of two identical tables of some components

Anonymous — Thu, 17 Mar 2022 09:55:55 GMT

Hi Tamerj1,

Sure, Thank You

Re: Need to show a table visual which shows difference of two identical tables of some components

Anonymous — Sat, 19 Mar 2022 07:09:41 GMT

Hi Tamerj1,

Please share the webex/some link to connect with you. Will join the invite

Re: Need to show a table visual which shows difference of two identical tables of some components

tamerj1 — Sat, 19 Mar 2022 16:21:17 GMT

Anonymous

I am extremely sorry. For some reason I did not recieve a notification for this message but only now!

Re: Need to show a table visual which shows difference of two identical tables of some components

tamerj1 — Sat, 19 Mar 2022 16:37:08 GMT

Hi Anonymous

please check your private messages