Forum Discussion

davidwoodward's avatar
davidwoodward
Frequent Visitor
6 years ago
Solved

Challenge with my data in Column Chart

I have some data that looks like the following   ID Date Opened Closed 1 1-Jan 1 0 1 1-Feb 0 1 2 1-Jan 1 0 2 1-Feb 0 1 3 1-Jan 1 1 4 1-Jan 1 0 4 1-Feb ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Is this the result you want.  Download my PBI file from here.

    Hope this helps.

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Please try these measure expressions in a visual with the Date column.

 

Open = SUMX(FILTER(Table, Table[Opened]<>Table[Closed]), Table[Opened])

 

Closed = SUMX(FILTER(Table, Table[Opened]<>Table[Closed]), Table[Closed])

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

davidwoodward's avatar
davidwoodward
Frequent Visitor
6 years ago

The result was that it excluded all cases where Open and Closed was equal to 1, but I need them included.

 

I changed the filter to Table[Closed]=Table[Closed] and that worked, but seems like an odd filter.  Should I do that a different way?

 

First time I've used a Measure, so new terratory for me.  I appreciate your ideas.