- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- HELP: Nobel prize award!!!!!

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

BiMK

Regular Visitor

HELP: Nobel prize award!!!!!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-16-2018
06:22 AM

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

v-qiuyu-msft

Moderator

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2018
12:16 AM

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

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.

If this post

BiMK

Regular Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-18-2018
12:49 AM

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

v-qiuyu-msft

Moderator

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-19-2018
02:28 AM

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

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.

If this post

BiMK

Regular Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-19-2018
08:26 AM

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

v-qiuyu-msft

Moderator

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-20-2018
02:53 AM

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 =

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

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.

If this post

BiMK

Regular Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-20-2018
07:13 AM

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

v-qiuyu-msft

Moderator

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-21-2018
01:52 AM

Hi @BiMK,

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

If this post

BiMK

Regular Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-23-2018
12:53 AM

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?

v-qiuyu-msft

Moderator

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-28-2018
12:29 AM

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

If this post