How to create Detailed report without having active relationship on date dimension?

[sonuojha1]

Hello All,

I have a requirement to drill through from the master report to the detailed report.

I have the data model as below:-

In my data model, I have many date fields in my transaction table, so I had joined all inactive mode and used those in measures as required.

Now I need to create a detailed report, which can be linked to my master report.

Kindly note: to make the relationship active I used userelationship in dax calculation.

Let say in my master report I have charts, matrix, and table visuals. 

Mostly visuals are as yearly - monthly data including company

So how would I drill through from Invoices posted by company report to detail report based on the selected month.

Data Model:

Master report example

Invoiced posted by Company
Company	C1	C2	C3	C4
JAN	78122	32	2	3
FEB	345	12	3	345
MAR	23	43	1	23
APR	55	55	55	55
MAY	67	67	5	67
JUN	8	76	65	98
JUL	45	56	80	21
AUG	87	14	3	8
SEP	321	12	5	4
OCT	56	32	7	5
NOV	4	73	1	8
DEC	7	7	4	7

Expected Result: 

INVOICE NUM	INV AMT	INV DATE	POSTED DT	DESC
1	1$	1-Jun	29-Jul	Inovoice posted successfully
2	2$	2-Jun	29-Jul	Inovoice posted successfully
3	1$	3-Jun	29-Jul	Inovoice posted successfully
4	2$	4-Jun	29-Jul	Inovoice posted successfully
5	1$	5-Jun	29-Jul	Inovoice posted successfully
6	2$	6-Jun	29-Jul	Inovoice posted successfully
7	1$	7-Jun	29-Jul	Inovoice posted successfully
8	2$	8-Jun	29-Jul	Inovoice posted successfully
9	1$	9-Jun	29-Jul	Inovoice posted successfully
10	2$	10-Jun	29-Jul	Inovoice posted successfully
11	1$	11-Jun	29-Jul	Inovoice posted successfully
12	2$	12-Jun	29-Jul	Inovoice posted successfully
Total Invoices Count: 56

Your help would be appreciated.

Regards,

Sonu

[v-alq-msft]

Hi, @sonuojha1 

Based on your description, I created data to reprodue your scenario. The pbix file is attached in the end.

Table:

Table2:

You may create a measure as below.

Visual Control = 
IF(
    FORMAT(MAX(Table2[INV DATE]),"mmm yyyy") in DISTINCT('Table'[YearMonth])&&
    MAX('Table2'[Company]) in DISTINCT('Table'[Company]),
    1,0
)

Then you need to put the measure in the visual level filter to get the result.

Finally when you drill through from 'Page1' to 'Page2', the visual will be filtered as what you want.

Best Regards

Allan

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

[sonuojha1]

@v-alq-msftThanks for reply.

I have a query

 Why you used two different tables for Summary(page1) and Detail report(page2)..

I have attached sample pbix file with some appropriate data including. Now please have a look and let me know how would I achieve my expected result while drilling through.

https://drive.google.com/file/d/1UCU9KzrcL2MDWn3nvaTzqLntnL3uIkUj/view?usp=sharing

[sonuojha1]

Adding more image:-

Drill through from master report to Detail report( C2 July 56--> drill to detail)