Cross Filtering not working as expected.

[JimJim]

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:

Total WIP = CALCULATE(COUNTROWS(Appointments),Appointments[IsWIP]=1)

Total WIP < 2 = CALCULATE(COUNTROWS(Appointments),Appointments[IsWIP]=1,Appointments[WIP Days] <=2)

Total WIP > 2 = CALCULATE(COUNTROWS(Appointments),Appointments[IsWIP]=1,Appointments[WIP Days] >2)

I have a matrix like this one which shows the number of WIP of each area and split of < 2 days and > 2 days

I then have a table which shows the details of each row, I would like to be able to see the four rows that are < 2 days or the two rows that are > 2 days. But when I select the values 4 or 2 it filters the table to show all six rows. I'm guessing that I haven't modelled my data correctly. 

Any help would be appreciated.

[vanessafvg]

@JimJim  can you can provide some sample data in text format?

[JimJim]

Actually @vanessafvg I have added it to onedrive

https://1drv.ms/u/s!ArazqhFmvkSJlgp8E5ljt0X_1CXE?e=qHp0Wl 

[vanessafvg]

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]

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]

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]

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.

[JimJim]

Hi @vanessafvg , I have an example report I want to upload but I'm not sure if I am able to do it