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

if a date falls under certain time period, return value from another table

Hi Powerbi friends,

 

i have a 2 tables

trascation table (direct query, can't be transform )

consist of 3 columns: date of transcation, service calls, actual CWE 

 

target table ( I created this table myself in powerbi)

I entered a date in each vessel call for the purpose of identifing each Quarter.

please see image

 

https://drive.google.com/open?id=1XcdAbDFiQjUjbMkz5-l60xiqWXqfds98 


how can i create a column/measure in the transcation table that returns a target CWEvalue from the target table.

 

if a transcation date falls between a Quarter in the transcation table, then match the corresponding service call in the target table and give target CWE vaule for that specific Quarter.

 

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try to create two quarter columns in both two tables:

Quarter = QUARTER('Transcation Table'[Date])

Quarter = QUARTER('Table'[Date])

Then create their relationship as many-to-many by [Quarter] link column:

2.jpg

Then try this measure:

Measure = 
CALCULATE (
    MAX ( 'Table'[Target CWE] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Service Calls] IN FILTERS ( 'Transcation Table'[Service Calls] )
            && 'Table'[Quarter] IN FILTERS ( 'Table'[Quarter] )
    )
)

The result shows:

32.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

 

View solution in original post

5 REPLIES 5
v-gizhi-msft
Community Support
Community Support

Hi,

 

Please try to create two quarter columns in both two tables:

Quarter = QUARTER('Transcation Table'[Date])

Quarter = QUARTER('Table'[Date])

Then create their relationship as many-to-many by [Quarter] link column:

2.jpg

Then try this measure:

Measure = 
CALCULATE (
    MAX ( 'Table'[Target CWE] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Service Calls] IN FILTERS ( 'Transcation Table'[Service Calls] )
            && 'Table'[Quarter] IN FILTERS ( 'Table'[Quarter] )
    )
)

The result shows:

32.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

 

amitchandak
Super User
Super User

I think you should able to create a Common date table and work with IT. Typically in the mixed-mode, it is allowed. I have not tried Mixed. But refer example of Direct query

 

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...

 

Anonymous
Not applicable

I do have a date table created by my IT department.

the date table also consist of quarters as well

but i'm not sure how the date table will help in this situation.

 

Hi,

 

Did my first reply solved your issue?

If so, please mark it as a solution for others to see.

Thanks!

 

Best Regards,

Giotto

Hi,

 

If you have seperate date table, you can create relationships between them.

And then you can get target data in one table from another table.

Please check my previous reply.

 

Best Regards,

Giotto

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.