Get Column Values ignoring Relationship Mapping using DAX

[Anonymous]

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

[Ashish_Mathur]

Hi,

Why do you have two calendar tables in the first place?

[Anonymous · Accepted solution]

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.