I have 2 csv files having sales data for different countries.I have same data available for india and srilanka in both the files.
now when i select india from slicer it should not show duplicate data and should show data from any one of the file and likewise for srilanka.
Also when i do not select any of these countries then it should not add up the duplicate values should show me values from any one of the file.
How can i acheive this in power bi?
From your original data , when you select India and select no Call Center ,the value for Sales is 1000 , which is correct .So why did you want it 500 ? Can you explain in detail ?
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@BA_Pete , I forgot to mention that I cant remove these records as these 2 files are coming from 2 different call center so lets say i have 2 call center A and B, so when I select call center A and then choose india or srilanka it will give me correct number.Also If i select Call center B and then choose india or srilanka then also it would give me correct number but when I do not select any call center then the values get added and i dont want that.
Hi @nj17 ,
So, the India rows in the call centre A file hold completely different information from the India rows in the call centre B file? They are not duplicated information, you just want to stop them being added together when you filter your report on India?
If this is the case, then I would still suggest appending your two tables as this generally makes modelling and calculations simpler, then you can create distinct measures for each of your different values, something like this:
_callCentreA_srilanka = CALCULATE( DISTINCTCOUNT(appendedTable[callNumber]), appendedTable[country] = "Sri Lanka", appendedTable[callCentre] = "Call Centre A" ) _callCentreB_srilanka = CALCULATE( DISTINCTCOUNT(appendedTable[callNumber]), appendedTable[country] = "Sri Lanka", appendedTable[callCentre] = "Call Centre B" ) _callCentreA_india = CALCULATE( DISTINCTCOUNT(appendedTable[callNumber]), appendedTable[country] = "India", appendedTable[callCentre] = "Call Centre A" ) _callCentreB_india = CALCULATE( DISTINCTCOUNT(appendedTable[callNumber]), appendedTable[country] = "India", appendedTable[callCentre] = "Call Centre B" )
You can then display these values completely independently from one another.
I have appended the data.this is the sample source file
In Power BI
I have added scenarios what I want:
1.When I select India and A then I get 500 which is correct
2.When I select India and B i get 500 which is also correct
3.but when I do not select any call center then I get 1000 which is a double counting for me I want it 500.how can I achieve this?
Hi @nj17 ,
I'm struggling to follow your use-case here, but if you want to change measure behaviour based on a slicer selection, then you could use a measure like this:
_measure = IF( HASONEVALUE(yourTable[Call Centre]), [totalSales], //Function you want to apply if no slicer selection, like average or Call Centre A sales only etc. )
Hi @nj17 ,
It sounds like both of your files have the same columns in them (same names and capitalisation) and, presumably, each have the same data types as one another.
In this case, I would APPEND these two files together in Power Query then select the whole appended table (Ctrl+A) and remove duplicates.
Use this new appended table as your data source in your report.
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.