Forum Discussion
Get Column Values ignoring Relationship Mapping using DAX
Hi All,
I have a Country table with the following values:
| Countries | Date | Values |
| Singapore | Apr-23 | 10 |
| Singapore | May-23 | 15 |
| Hong Kong | Apr-23 | 20 |
| Australia | May-23 | 25 |
I have two calendar Tables. Calendar 1 and Calendar 2. Calendar 1[Date] column has a One to Many relationship mapping with Country [Date] table.
In one of the report pages, I have both the calendar tables in the filter selection. Calendar 1 [Date] is selected as Apr - 23 and Calendar 2 [Date] is selected as May - 23. I want to show the table as below. The Calendar 1 Date should be ignored and Calendar 2 Date should be considered. Please let me know if there is a way to achieve this.
| Countries | Date | Values |
| Singapore | May-23 | 15 |
| Australia | May-23 | 25 |
Hi Anonymous ,
Please try below steps:
1. below is my model
2. create a measure with below dax formula
Measure = VAR _date1 = SELECTEDVALUE ( Calendar1[Date] ) VAR _date2 = SELECTEDVALUE ( Calendar2[Date] ) VAR _date = SELECTEDVALUE ( 'Country'[Date] ) VAR _val = SWITCH ( TRUE (), ISFILTERED ( Calendar1[Date] ) && ISFILTERED ( Calendar2[Date] ), _date2, ISFILTERED ( Calendar1[Date] ) && NOT ( ISFILTERED ( Calendar2[Date] ) ), _date1, ISFILTERED ( Calendar2[Date] ) && NOT ( ISFILTERED ( Calendar1[Date] ) ), _date2, NOT ( ISFILTERED ( Calendar2[Date] ) ) && NOT ( ISFILTERED ( Calendar1[Date] ) ), _date ) RETURN IF ( _date = _val, 1 )3. add two slicer with Calendar1 and Calendar2 table field, add a table visual with Country table fields, add measure into table visual filter then set and apply
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
Please try below steps:
1. below is my model
2. create a measure with below dax formula
Measure = VAR _date1 = SELECTEDVALUE ( Calendar1[Date] ) VAR _date2 = SELECTEDVALUE ( Calendar2[Date] ) VAR _date = SELECTEDVALUE ( 'Country'[Date] ) VAR _val = SWITCH ( TRUE (), ISFILTERED ( Calendar1[Date] ) && ISFILTERED ( Calendar2[Date] ), _date2, ISFILTERED ( Calendar1[Date] ) && NOT ( ISFILTERED ( Calendar2[Date] ) ), _date1, ISFILTERED ( Calendar2[Date] ) && NOT ( ISFILTERED ( Calendar1[Date] ) ), _date2, NOT ( ISFILTERED ( Calendar2[Date] ) ) && NOT ( ISFILTERED ( Calendar1[Date] ) ), _date ) RETURN IF ( _date = _val, 1 )3. add two slicer with Calendar1 and Calendar2 table field, add a table visual with Country table fields, add measure into table visual filter then set and apply
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi,
Why do you have two calendar tables in the first place?
