How to get column values from table which is having many to may relationship?

[Anonymous]

I have two tables which is having many to many relashionships as shown below

 

I have created my DateRange table by below power query.

Time range Table


let
    Source = List.Times(#time(0,0,0) , 1440, #duration(0,0,30,0)),
    convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), {"TimeRange"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(convertToTable,{{"TimeRange", type time}})
in
    #"Changed Type"


DateRange Table


let
    StartDate = #date(2018,5,1),
    EndDate = #date(2018, 5, 31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date", type date}}, "en-IN"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each TimeRange),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"TimeRange"}, {"TimeRange"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"TimeRange", type time}})
    
in
    #"Changed Type1"

Now i have created two calcualted columns in Daterange table as follows.

SelectedDate-Time = 
VAR maxDate = CALCULATE(MAX('DateRange'[Date]),ALLEXCEPT('DateRange',DateRange[Date],DateRange[TimeRange]))
VAR maxTime = CALCULATE(MAX('DateRange'[TimeRange]),ALLEXCEPT('DateRange','DateRange'[Date],DateRange[TimeRange]))
RETURN
    maxDate&" "&maxTime

NextDate = ((DateRange[SelectedDate-Time]+1)-TIME(0,0,1))

Now i would like to filter out the May table Date-Time column which are between SelectedDate-Time and NextDate

i.e    "IF(May[Date-Time] >= DateRange[SelectedDate-Time] && May[Date-Time] <= DateRange[NextDate], May[Date-Time],Blank)"

I tried the same calculated column in MAY table with if function but got no luck.

Column = IF(May[Date-Time] >= MAX(DateRange[SelectedDate-Time]) && May[Date-Time] <= MAX(DateRange[NextDate]),May[Date-Time])

Sample Date :- https://1drv.ms/x/s!AhiQ2f7YQHC-gbNZLawDDsorPpzKaQ

Please suggest me.

Thanks 

Mohan V

[Anonymous · Accepted solution]

Hi @Anonymous,

You can refer to below steps to achieve your requirement.

1. Break relationship from date range table to 'May' table.

2. Write a measure to check current selection from date range table and return tag.

Is Range =
VAR currDate =
    MAX ( May[Date-Time] )
VAR _start =
    MAX ( DateRange[SelectedDate-Time] )
VAR _end =
    MAX ( DateRange[NextDate] )
RETURN
    IF ( currDate >= _start && currDate <= _end, "Y", "N" )

3. Create table visual based on 'May' table, drag above measure to visual level filter to filter matched records.

Regards,

Xiaoxin Sheng

[Anonymous]

@Anonymous thank you soo much.

I literally lost the hope that i will get help from community but you proved me wrong.

Thanks you soo much.

I tried what you have suggested and it worked like a charm.

Im extreamly Sorry that i am extending this thread, but if you could help me with this then,

I will be sooooooo thankful to you.

Im trying to calculate the MIN and MAX timings from the same filtered values using below dax calculated column.

Min = CALCULATE(
	MIN('May'[Date-Time]),
		ALLEXCEPT('May','May'[EMP_ID],'May'[BEGIN_DATE])
	
)

But the output is something unexpected.

I even tried the measure with same dax but both giving the same result.

Please please please suggest me.

Thanks in advance.

Mohan V

[Anonymous]

Hi @Anonymous,

Current power bi not support to create dynamic calculated column/table based on slicer/filter. I think you need to measure to instead.

Calculated Column/Table Change Dynamically According to Slicer Selection in the Report.

Regards,

Xiaoxin Sheng