cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BiMK Regular Visitor
Regular Visitor

HELP: Nobel prize award!!!!!

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).

 

 

Screenshot_54.jpg

 

 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)

Screenshot_58.jpg

 

 

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

Screenshot_57.jpg

 

If anyone has ANY ideas on how can I solve it I would be forever gratefull!

THANKS!!

10 REPLIES 10
Moderator v-qiuyu-msft
Moderator

Re: HELP: Nobel prize award!!!!!

Hi @BiMK,

 

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"))))

 

w1.PNG

 

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BiMK Regular Visitor
Regular Visitor

Re: HELP: Nobel prize award!!!!!

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

 

 

Moderator v-qiuyu-msft
Moderator

Re: HELP: Nobel prize award!!!!!

Hi @BiMK,

 

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"))))

 

q10.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BiMK Regular Visitor
Regular Visitor

Re: HELP: Nobel prize award!!!!!

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).

 

Screenshot_60.jpg

 

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.

Screenshot_62.jpgScreenshot_63.jpg

 

 

 

 

 

 

 

 

 

 

 

 

But if I don't filter the date and leave it as it is the result is this:

 

Screenshot_64.jpg

 

How can I solve this? Smiley Sad 

 

Thanks again for your help!!

 

https://drive.google.com/file/d/1wBIbq44XZpLRnt1Zrsas8Nzxx3TQRlFZ/view?usp=sharing

Moderator v-qiuyu-msft
Moderator

Re: HELP: Nobel prize award!!!!!

Hi @BiMK,

 

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: 

 

q3.PNG 

 

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]))

 

 

q6.PNGq7.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BiMK Regular Visitor
Regular Visitor

Re: HELP: Nobel prize award!!!!!

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:Screenshot_67.jpg

But, when I select two or more it's not..

 

Screenshot_68.jpg

 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.

Screenshot_69.jpg

 

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

 

Screenshot_72.jpg

Any idea why this is happening?

 

Thanks again Smiley Happy

 

https://drive.google.com/file/d/1W2PGWOQonleqq7ZsQRhJmLfIDwPVBOOy/view?usp=sharing

Moderator v-qiuyu-msft
Moderator

Re: HELP: Nobel prize award!!!!!

Hi @BiMK,

 

Please add a new record into Table2: 

 

w2.PNG

 

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: 

 

w3.PNG

 

 

Best Regards,
Qiuyun Yu 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BiMK Regular Visitor
Regular Visitor

Re: HELP: Nobel prize award!!!!!

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?

Moderator v-qiuyu-msft
Moderator

Re: HELP: Nobel prize award!!!!!

Hi @BiMK,

 

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 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.