Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
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.
Solved! Go to Solution.
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.
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
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
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 (
Please let me know if above explanation gives enough Insight about my requirement.
Thanks again.
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |