Want to create a button in Direct Query Mode that shows Ranges (years, months, days)

[Anonymous]

Hi everyone,

I used the below formula to create a table (which isn't neccesarily required) in Import mode, the need was for a button that shows the Range (years months days) for the period - see below picture.

Now I have changed the source to Direct Query mode, but I am not able to create a table inorder to use this formula for the button. So instead I'd like to create a measure? so that I can still use it for the button. I'm not sure how to do this.

When I drop the below formula into a measure in direct query mode it produces an error:

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

I am guessing that there needs to be a SUMX formula in there? (I'm a beginner so I'm not sure how I'd even write this)

Formula:

Ranges =

VAR StartDate = MIN ('Run Time'[StartTime])

VAR EndDate = MAX ('Run Time'[EndTime])

RETURN

{

("Number of Years", DATEDIFF(StartDate, EndDate, YEAR)),

("Number of Quarters", DATEDIFF(StartDate, EndDate, QUARTER)),

("Number of Months", DATEDIFF(StartDate, EndDate, MONTH)),

("Number of Days", DATEDIFF(StartDate, EndDate, DAY))

}

//this formula generates totals for Years, Quarters, Months, Days

 Thanks in advance for your help 🙂

[amitchandak · Accepted solution]

@Anonymous , Try like

Ranges =
VAR StartDate = MIN ('Run Time'[StartTime])
VAR EndDate = MAX ('Run Time'[EndTime])
RETURN
ConcatenateX({
("Number of Years", DATEDIFF(StartDate, EndDate, YEAR)),
("Number of Quarters", DATEDIFF(StartDate, EndDate, QUARTER)),
("Number of Months", DATEDIFF(StartDate, EndDate, MONTH)),
("Number of Days", DATEDIFF(StartDate, EndDate, DAY))
}, [Value], ", ")

[Anonymous]

Thank you for this, [Value] doesnt exist. There instead appears to be two values, [Value1] and [Value2], so I made two seperate measures for each value. It comes out like this:

I ask how this could be improved at all?