Forum Discussion

VipulSingh's avatar
VipulSingh
Frequent Visitor
9 years ago

Handling Blank values from a Measure

Hi,

 

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

 

UserIdCategoryReportNameCYearMonthPreMonthViewsCurMonthViewsCurrentMonthPreviousMonth
aabadveOSADOSADNext2017045143
aabadveOSADOSADNext2017030532
aalapcMetricalActivityDrillDownWithMetricsAPI/ErrorCodes20170281021
aalapcMetricalActivityDrillDownWithMetricsAPI/ErrorCodes20170310232

 

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.

Need Help

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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?:smileyhappy:

     

    Regards

    • VipulSingh's avatar
      VipulSingh
      Frequent Visitor
      9 years ago

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

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

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

     

    Regards