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

[Anonymous]

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

[Anonymous]

Hi Tamerj1,

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

[tamerj1]

Hi @Anonymous 

please check your private messages

[tamerj1]

@Anonymous 

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

[tamerj1]

hi @Anonymous 

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

[tamerj1]

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. 

[Anonymous]

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?

[Anonymous]

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 )

[tamerj1]

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.

[Anonymous]

Hi Tamerji1,

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

[tamerj1]

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

[Anonymous]

Hi tamerj1,

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

[tamerj1]

Hi @Anonymous 

Would be possible only on next Saturday. 

[Anonymous]

Hi Tamerj1,

Sure, Thank you 🙂

[Anonymous]

Hi Tamerj1,

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

[Anonymous]

Hi Tamerj1,

Sure, Thank You