cancel
Showing results for
Did you mean:
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).

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!!

10 REPLIES 10
Moderator

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

Hi @BiMK,

Regarding your Quantity formula, you can create a measure below:

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

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

Moderator

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

Hi @BiMK,

You can create a calculated column:

return
IF(t=0, "=0", IF(t=1, "=1", IF(t=2, "=2", IF(t=3, "=3", ">3"))))

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

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?

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:

Then replace the Columns bucket value from Column2 to the Column1 in this new table. Then create a measure below:

Measure =
return

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

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

Moderator

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

Hi @BiMK,

Then modify the measure:

Measure =
return

Drag the TaskID column to matrix Columns bucket:

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

## 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?