Handling Blank values from a Measure

[VipulSingh]

Hi,

Refer below sample data, which is aggregated data for a month for a User/Category/ReportName.

UserId	Category	ReportName	CYearMonth	PreMonthViews	CurMonthViews	CurrentMonth	PreviousMonth
aabadve	OSAD	OSADNext	201704	5	1	4	3
aabadve	OSAD	OSADNext	201703	0	5	3	2
aalapc	Metrical	ActivityDrillDownWithMetricsAPI/ErrorCodes	201702	8	10	2	1
aalapc	Metrical	ActivityDrillDownWithMetricsAPI/ErrorCodes	201703	10	2	3	2

we need to show top 5 new/dropped users/teams on the number of views by repective user by refering "PreMonthViews" and "CurMonthViews".

Above sample is part of our base dataset. We get Team Name from another table.

To get new user/team we have created measure to sum previous month views, which needs to be 0 and the corresponding current month views should be greated than 0.

PreView. = sum(Usage_NewTeam_Data[PreMonthViews]) 

by using "PreView.=0" in  visual level filter we are able to get List of new teams,like below

with visual filter "PreViews. is 0"

We have multiple filter in the report which will be selected from slicers, which a user can select and this list will be refreshed accordingly.

To select top 5 from the various filters selected, we tried to create below measures and table:

CurView. = sum(Usage_NewTeam_Data[CurMonthViews])

CRankTeam = RANKX(ALLSELECTED(Usage_NewTeam_Data[Team Name]),[CurView.])

CTopViews. = if(HASONEVALUE(Usage_TopN[Top]),if([CRankTeam]<=VALUES(Usage_TopN[Top]),[CurView.]))

Usage_TopN = UNION(
row("Top",5),
row("Top",10),
row("Top",15))

which results in(refer below): right side table

with visual filter "PreViews.=0"

We could see, only top 5 have values as selected from the slicer, not for others, is there a way to ignore all the other values altogether.

Also, If we do not select PreView. as Value, the table goes blank.

How to get only top N teams visibility only?

Correct us if we are missing something or Suggest us a workaround where we could use a measure in the visual filter and will be able to get top N from the filters selected in the reports.

Let us know for clarification.

[v-ljerr-msft]

Hi @VipulSingh,

Could you try removing the [PreView.] from your Table/Matrix visual on the report(you should also be able to use "PreView.=0" in  visual level filter) to see if it works?

If it still doesn't work, could you share a sample pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[v-ljerr-msft]

Hi @VipulSingh,

Have you tried adding the measure "CTopViews" also as Visual Level Filter with "CTopViews is not blank" to see if it works in your scenario?

Regards

[VipulSingh]

Tried that option, but it does not help. Nothing changes with the filter