Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
@JimJim can you can provide some sample data in text format?
Proud to be a Super User!
I would probably change this to a grouping rather than a measure
for eg
Proud to be a Super User!
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.
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",
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.
Hi @vanessafvg , I have an example report I want to upload but I'm not sure if I am able to do it
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |