Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello guys,
I have been dealing with this issue for the past month and I am out of any indeas on how to solve it.
So, what I am trying to present in Power BI is a table which shows how many of the tasks were reopened 0,1,2,3 and more than 3 times. Basically, if I have 5 tasks in category 1 that were reopened once and 3 tasks in category 2 that were reopened more than 3 times, the table below would show 5 in the cell Category 1 : =1 and 3 in the cell Category 2 : =3 (see img below).
I have 7 other filters on the dashboard (one of which is date) which are applied on this table. The granularity of the source table I am using for this dashboard is a task (example below)
The result from this would be 1 task with 3 reopenings and 1 task with 2 reopenings (if we are considering the whole dataset and not filtering by date).
I tried creating a derived column
Quantity = IF(SUM(Table[Reopenings])=0, "=0", IF(SUM(Table[Reopenings])=1, "=1", IF(SUM(Table[Reopenings])=2, "=2", IF(SUM(Table[Reopenings])=3, "=3", ">3"))))
but I am getting completely wrong values
If anyone has ANY ideas on how can I solve it I would be forever gratefull!
THANKS!!
Hi @Anonymous,
Regarding your Quantity formula, you can create a measure below:
Measure = VAR t=CALCULATE(SUM(Table1[Reopenings]),FILTER(ALLSELECTED(Table1),'Table1'[TaskID]=MAX('Table1'[TaskID])))
return
IF(t=0, "=0", IF(t=1, "=1", IF(t=2, "=2", IF(t=3, "=3", ">3"))))
Besides, from your description, there should be a column store values for category. But from your second screenshot, there is no category column field. If above DAXdoesn't meet your requirement, please share dummy data table with us and clarify corresponding desired results.
Best Regards,
Qiuyun Yu
Hello @v-qiuyu-msft,
Thank for the suggested solution. However, I need it to be a column and not measure because I need to show it as a matrix table (first picture). Also, the second picture is a screenshot of the data source and no, I can not have a column with category because depending on the filters selected the Quantity column needs to change correspondingly.
Here is an example report with dummy data.
https://drive.google.com/file/d/1hI6tbIat7lSFBKE3woHlxk1tIAhaBqKf/view?usp=sharing
Hi @Anonymous,
You can create a calculated column:
Column = VAR t=CALCULATE(SUM('Table'[Reopenings]),FILTER(ALLSELECTED('Table'),'Table'[TaskID]=EARLIER('Table'[TaskID])))
return
IF(t=0, "=0", IF(t=1, "=1", IF(t=2, "=2", IF(t=3, "=3", ">3"))))
Best Regards,
Qiuyun Yu
Hey @v-qiuyu-msft,
Thanks a lot for helping! The only issue that appears now is when I want to filter by the filters on the dashboard (Date, Filter 1,2,3..).
For the sake of understanding better, I am using only one TaskID (see img).
As you can see, this TaskID has different date values so when filtered by "15-07-18", the "Column" needs to be "=2". Correspondingly, when filtered by "16-07-18" it needs to be "=1". However, when I do that, the column stays "=3" and I understand that's because of the use of the ALLSELECTED function. So, I replaced ALLSELECTED with ALLEXCEPT(Table, Table[Date]) and I got the correct values.
But if I don't filter the date and leave it as it is the result is this:
How can I solve this?
Thanks again for your help!!
https://drive.google.com/file/d/1wBIbq44XZpLRnt1Zrsas8Nzxx3TQRlFZ/view?usp=sharing
Hi @Anonymous,
In your scenario, as the matrix column group values comes from a column "Column2" which will not be updated based on slicer selection, when you select all date, it will not display =3.
You can create a new table with data below:
Then replace the Columns bucket value from Column2 to the Column1 in this new table. Then create a measure below:
Measure =
VAR t = CALCULATE(SUM('Table'[Reopenings]),FILTER(ALLSELECTED('Table'),'Table'[TaskID]=MAX('Table'[TaskID])))
return
IF(t=MAX('Table2'[Value]),COUNT('Table'[TaskID]))
Best Regards,
Qiuyun Yu
Hey @v-qiuyu-msft,
This works perfect if I am showing one task at a time.. However, In my case I need to show a sum of all the tasks with corresponding reopenings.
For example, for one task (12345) it's correct:
But, when I select two or more it's not..
In this case I would need to have two columns, =1 and =3, each having 1 as value as 12345 has 3 reopenings and 2342333 has 1 reopening so the number of tasks with the corresponding number of reopenings is 1 in each.
Another problem I encountered with is when chosing taskID=6789. This one has 4 reopenings so it needs to be under ">3", but wasn't showing anything in the table.
For this purpose, I added >3 with values from 4-30 in your table but it still doesn't show anything..
Also, I noticed that when I select all the tasks (except for 6789) i got the following result
Any idea why this is happening?
Thanks again 🙂
https://drive.google.com/file/d/1W2PGWOQonleqq7ZsQRhJmLfIDwPVBOOy/view?usp=sharing
Hi @Anonymous,
Please add a new record into Table2:
Then modify the measure:
Measure =
VAR t = CALCULATE(SUM('Table'[Reopenings]),FILTER(ALLSELECTED('Table'),'Table'[TaskID]=MAX('Table'[TaskID])))
return
IF(t=MAX('Table2'[Value]) || (t>MAX('Table2'[Value]) && MAX('Table2'[Value])>3),COUNT('Table'[TaskID]))
Drag the TaskID column to matrix Columns bucket:
Best Regards,
Qiuyun Yu
Hello @v-qiuyu-msft,
I can not add the TaskID to the category because there are a lot of Tasks and I need a sum.. So this solution does not work well for the requirements I have :/. Any ideas for another way to solve this?
Hi @Anonymous,
If you don't drag the TaskID in the Column bucket, is it above solution meet your requirement?
If not, would you please clarify your desired results?
Best Regards,
Qiuyun Yu
Hello @v-qiuyu-msft,
I clicked accept as solution by mistake, sorry..
If I don't drag TaskID in the table the solution is not fit. The desired result is to get the total number of tasks with 0,1,2,3 and more than 3 reopenings by Filter1 (imagine this as a team of people, I have 4 teams), which means that I can not have the TaskID in the table since I will have thousands of tasks.
Do you think there is any solution for this?
Thanks a lot!
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |