Forum Discussion

JimJim's avatar
JimJim
Responsive Resident
6 years ago

Cross Filtering not working as expected.

Hi Guys,   I have a report that shows the number of appointments that are at stage work in progress (WIP) and also how many of these are >2 days. I have the following measures on my report:   Tot...
Need Help
vanessafvg's avatar
vanessafvg
Community Champion
6 years ago

I would probably change this to a grouping rather than a measure

 

for eg

 

WIP Days Group = switch (true(),
Appointments[IsWIP] = 1 && Appointments[WIP Days] < 2, "< 2 Days",
Appointments[IsWIP] = 1 && Appointments[WIP Days] > 2, "> 2 Days",
"Not <2 or >2")
 
and then its easy to apply this as a filter 

 

 

JimJim's avatar
JimJim
Responsive Resident
6 years ago

Hi vanessafvg 

 

Thanks for your reply.

 

When I add the measure you provided I get the following error message:

 

A single value for column 'IsWIP' in table 'Appointments' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi JimJim ,

     

    If you want to create measure, you need to use SELECTEDVALUE() function to wrap your column.

    SELECTEDVALUE( Appointments[IsWIP] ) = 1 && SELECTEDVALUE( Appointments[WIP Days] ) < 2, "< 2 Days",
SELECTEDVALUE( Appointments[IsWIP] ) = 1 && SELECTEDVALUE( Appointments[WIP Days] ) > 2, "> 2 Days",

     

    • JimJim's avatar
      JimJim
      Responsive Resident
      6 years ago

      Hi v-eachen-msft , thank you for the tip. I didn't know about the SELECTEDVALUE function.

      I managed to create a new column which shows the WIP status, it looks like having a different column for each status was causing the problem.

       

      Thanks to both you and vanessafvg for your help.