Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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:
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:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto
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:
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:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto
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
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |