Cant use a datecolumn from table in a measure

[darko_ivanovic]

Hi Community!

I'm gonna quick introduce you to my "chaotic" datamodell, its about "issues". 

I have a 'Date Table' which contains all the dates. I have a 'JiraIssue' Table which contains all the information about an issue like creationdate, resolveddate, status etc. And of that 'JiraIssue' table i have made a Copy -> 'JiraIssueCopy' with the exact same values and everything, the reason for that is that the original table is linked to the 'Date Table' over the Column [Status_Changedate]. And for Drilltrough reasons i had to make a copy of that table which is not connected to the Datetable, now i pass the month i want to drill trough with a SELECTEDVALUE. but to get the correct items in the drillpage i need Measures. 

Now i have following Measure which contains the last day of the selected month: 

LOOKUPVALUE('Date Table'[EndofMonth]; 'Date Table'[Month-Year]; 
                                    SELECTEDVALUE('Date Table'[Month-Year]))

And Following Measure im trying to create: 

IF('JiraIssueCopy'[CREATED] <= [Measure_EndofMonth]; 1; 0)

But it wont let me use the 'JiraIssueCopy'[CREATED] in the measure. 

Is there a way to use a datecolumn in a measure? or is there a workaround ? 

I need Help pls. 

[JosefPrakljacic]

Hello @darko_ivanovic ,

the problem is that the row-context is missing in your Measure.

Either you use a calculated column or you put this thing in an IteratorFunction like (AVGX, SUMX ...).

Then this thing will work. 🙂

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

BR,
Josef

[darko_ivanovic]

Hi Josef

Do you have an idea how i could "put this thing in an IteratorFunction" ? 

[JosefPrakljacic]

Hi Darko, 

I don't know your usecase exactly but as I see you just want to classify each row by 1 or 0.

So I would use a calculated column...

I don't see a point in using (AVGX, MAXX, MINX, SUMX etc for your use case)

But if you insist on an iterator function i would create a calculated table and do something like this

YourNewTable := ADDCOLUMNS(YourJiraTable,"MyNewCalcColumn", IF(ColumnOfYourJiraTable....))

An IteratorFunction Usage would for instance also look like this...

Measure:= MINX(YourJiraTable,YourIFStuff)

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

BR,
Josef