Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sairam_n
Frequent Visitor

Display blanks/null instead of Repeated Values in a hierarchy

Hello Techies,

 

We are having an issue with Power BI visual populating repetitive or duplicate values for lower granular data in a hierarchy of SVP > Director > Sales Rep as shown below:

Repetition of valuesRepetition of values

 

 We have disaggregated data available until Director so data is populating as expected until there and its populating duplicate records after that.

I have tried below DAX query to populate the Target data using HASONEFILTER:

 

QTR TargetBlank = if(HASONEFILTER('Sales Org'[Sales Rep]),BLANK(),[QTR Target])

 

Its going to bold font and its not allowing to drill down further after Director as shown below:

 

Result after using HASONEFILTERResult after using HASONEFILTER

 

We are actually supposed to keep other hierarchy attributes after Director like Sales Rep, Team Lead, Product etc.,

As I mentioned above, its simply populating duplicate values after ‘Director’ level.

Can someone recommend if there is any workaround we can try in this scenario.

 

Thanks in advance.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Visuals remove blank entries.  While its displaying the copied amount, its still a non-blank entry, thus you get 1 row per person.

 

When you use the measure that removes the value from the employees, you get null for those employees and the visual will not draw them.

 

You solution instead, will be to replace the null with a value you want to display.  Try replaying the BLANK() with "" to get the outcome you expect (although this will make your measure a string, rather than a number.  This is ok if you use this as your "Display" measure.  If you need QTR TargetBLANK as a calculation measure to be used in other places, simply create a 2nd measure to point to the first and convert blank to "".  Use that 2nd measure on your display visual.

View solution in original post

8 REPLIES 8
v-shex-msft
Community Support
Community Support

Hi @Sairam_n,

I think you need to add a new calculated column with if statement to group your records instead replace records values to blank. (It will aggregate your records on matrix with wrong category fields)

I'd like to suggest you share some dummy data with the expected result for further test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
vivran22
Community Champion
Community Champion

Is it possible to share the relationship view? Looks like there is no relationship after the Director level, hence the values are repeating.
Anonymous
Not applicable

Based on what I see, you must have a relationship between the table that contains the "SalesRep-PersonName" field and the field that contains the numerical information.  If you look at that relationship, does that relationship allow for filtering down to the SalesRep level?  It looks like the table structure you have set up, is at the DIR level.

Hello Ross,

 

Thanks for your insights. As you rightly mentioned, we have disaggregated data of QTR Targets available at Director level only. 

I understood about why is it populating repeated values however I would like to know if we can use some workaround to avoid this.

 

Thanks in advance.

 

regards,

Sairam 

Anonymous
Not applicable

Hi @Sairam_n, what work around are you expecting?  If you don't explicitly tell the data model how to relate the data at a sales person level, how will you expect it to know the correct amount per sales person?

@Anonymous , So we have QTR Target data available at Director level alone and its getting aggregated to next level of hierarchy as expected and the model has a join between Sales Org Table(where we have this hierarchy) and QTR Targets table based on Director ID and hence its expected behavior that its repeating values after Director level as it doesnt have data for Sales rep and lower hierarchies. 

So my requirement here is, to populate just Null/keeping it blank rather than populating Director level data for lower hierarchy attributes which confuses end users.

 

While I was working on this today, I found something intresting here. When I drag original measure QTR target (

QTR Target = SUM('Director Targets'[QTRTarget] )) and another measure QTR TargetBlank I have created with 'HASONEFILTER' function as below:
QTR TargetBlank = if(HASONEFILTER('Sales Org'[Sales Rep]),BLANK(),[QTR Target]) , its showing below output for QTRTargetBlank field which is expected :
 
**QTR TargetBlank field from below table is what I am expecting which just shows data at Director level and shows blank data for below Hierarchy attributes.

Expected outcome2.PNG
 
However when I remove original measure QTR Target, and keep QTR TargetBlank, its going back to Director level and not allowing me to drill down further
QTR TargetBlankOP.PNG

Please let me know if above explanation gives enough Insight about my requirement.

 

Thanks again. 

 
 
 
 
 
 

 

 

Anonymous
Not applicable

Visuals remove blank entries.  While its displaying the copied amount, its still a non-blank entry, thus you get 1 row per person.

 

When you use the measure that removes the value from the employees, you get null for those employees and the visual will not draw them.

 

You solution instead, will be to replace the null with a value you want to display.  Try replaying the BLANK() with "" to get the outcome you expect (although this will make your measure a string, rather than a number.  This is ok if you use this as your "Display" measure.  If you need QTR TargetBLANK as a calculation measure to be used in other places, simply create a 2nd measure to point to the first and convert blank to "".  Use that 2nd measure on your display visual.

Thanks for these Inputs @Anonymous  .. I tried this with some scenarios and seems to be working as expected 🙂

Thats great catch and Thanks again. I will use this approach to test it thoroughly.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors