cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
darko_ivanovic Frequent Visitor
Frequent Visitor

Cant use a datecolumn from table in a measure

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. 

3 REPLIES 3
JosefPrakljacic Established Member
Established Member

Re: Cant use a datecolumn from table in a measure

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 Frequent Visitor
Frequent Visitor

Re: Cant use a datecolumn from table in a measure

Hi Josef

 

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

JosefPrakljacic Established Member
Established Member

Re: Cant use a datecolumn from table in a measure

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 82 members 2,700 guests
Please welcome our newest community members: