• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. L

    Remove not used values, in dimensions

    Ok, by directly using List.Combine and List.Distinct functions, to get only the relevant data from fact tables, and then joining the result to the full data in the dimension, I'm able to quickly "reduce" the dimenston data :). However, I'm obliged to load my dimensions AFTER the fact tables...
  2. L

    Remove not used values, in dimensions

    Thank you for for both of you. I will use power query (and I already do that, but it is just that in Qlikview/QlikSense, you can do it more efficiently (according to me), by joining tables and telling during the join, to remove the unused value ('LEFT KEEP JOIN' instruction). So I'm wondering...
  3. L

    Remove not used values, in dimensions

    OK, thank you. Was aware of that indeed, but it is just a workaround according to me. Indeed, if you use the same field filter but on the pivot table, the list will be the 100 000. :( I would really want to reduce the list in the model, not in the interface. So solution would only be using...
  4. L

    Remove not used values, in dimensions

    Hello, Common problem I would want an advice... let says we have a dimension table 'DimCity' and a table 'Fact', linked together by cityID. * City contains 100 000 cities (city id, city name) * Fact only use 2 000 cities. When I put all of that in the model, and create a pivot table, and a...
  5. L

    Non numeric measure not available in a powerview ?

    Thank you for your checks. Vote +1 :) Good idea about positive and negative layers in 3D Maps.
  6. L

    Non numeric measure not available in a powerview ?

    I will check for matrix visual in power view, ok. But I guess you are talking about power bi, not. Excel/power view... Indeed the measure is NOT AVAILABLE in the field/measure list in the power view sheet. So not sure I will be able to use it even on a matrix visual... For the link, already...
  7. L

    Non numeric measure not available in a powerview ?

    Hello, In DAX (in Excel power pivot) you can create non numeric measures and display it in a pivot table. For example a dynamic measure called STATUS, that return « SURPLUS » if the sum of values is POSITIVE and return « DEFICIT » if the sum of value is NEGATIVE. I can easily add it in the...
  8. L

    Display sum of value for a TOPN selection

    OK found myself the answer :) There is a trick on the map that I need to do: * Add an additional measure with RANKX only. And this measure is added as a filter of the map, and I filter the measure with values <=10 (to get only the TOP10). For the table in powerview, there was an...
  9. L

    Display sum of value for a TOPN selection

    Please find attached a sample file. - DATA sheet contains sample data - PIVOT sheet contains a pivot table with the TOP 10 (and it is correct) - And Power view sheet contains a map and a table with normally the same data, but result is incorrect, it is showing more than my TOP 10 points :(
  10. L

    Display sum of value for a TOPN selection

    Hum... Actually I now manage to do it : Sum Values (TOP 10):= VAR SelectedTop = 10 RETURN SWITCH(TRUE(); SelectedTop = 0; [SumValues]; RANKX ( ALLSELECTED(FactImbalance[CITY]); [SumValues] ) <= SelectedTop; [SumValues] )...
  11. L

    Display sum of value for a TOPN selection

    Hello, I have a basic question: Suppose I have a table with 3 columns : City / Month / Value An I have 100 cities for example. I want to create a pivot table by using a DAX measure, for only the top 10 of cities, and have the sum of values for each of the 10. Actually I want simply do like...
  12. L

    Data with different granularity - basic sample - need advice

    Thank you. wel noted about the 2 measures, arnd the usage of DIVIDE function in case of null values. However, you use SUM, while I'm using SUMX, not usre in this case to understand the difference. I thought that it was obliged to use SUMX, little bit confusing for me :( So I understand that...
  13. L

    Data with different granularity - basic sample - need advice

    Hello, I want to have a dashbaord to calculate a perf between the number of sales by user, compared to a sale's objective that is by user. So I have a table that list all sales. A line by sale (and by user) and a table by user with the target desired for each user. I just want to compare the...
  14. L

    Power pivot : 2 fact tables with different granularity to be joined...

    OK solution found. Just have to create and add right dimensions tables city and type in my model, and link them to my 2 fact tables. Please check the file in attached, perhaps it could help someone.
  15. L

    Power pivot : 2 fact tables with different granularity to be joined...

    Hello, I try to read a lot about power pivot, but if I can easily understand some basics on simple models, in my real job models are more complex and I have 2 fact tables with different granularity. I have tried to see how to deal with that and found some articles on internet, but either...
  16. L

    How to clear old Pivot table items in filter lists (powerpivot pivot table) ?

    Well noted, However not sure to understand your proposal. Currently I use PQ to load data (fact and dimension) from my DB. So do you mean to : 1. add in PQ a calculated field (a flag column) in my dimension table to filter only the one that have data ? 2. (in PQ) Filter and removed unused data...
  17. L

    How to clear old Pivot table items in filter lists (powerpivot pivot table) ?

    Thank you for your answer. For me, it is a basic thing to filter facts data by a dimension table. I do the model for that I would say... :( Filter on the fact field is not really user friendly, the vessel_Id field is a code, so not really possible to filter on it. If the slicer had (like in...
  18. L

    How to clear old Pivot table items in filter lists (powerpivot pivot table) ?

    Actually You are right, I use a field of a dimension table where my value is present. BUT, there is NO data (from my fact table) related to this item ! If I add a slicer for this field, I can only see the items with related data, while it is not possible in the filter. (see picture attached...
  19. L

    How to clear old Pivot table items in filter lists (powerpivot pivot table) ?

    Hello, I'm facing a problem with a pivot table that is linked to a power pivot model. Twhen I try to filter a pivot item, Isee a lot of old unused items. While on a standard pivot table, I know how to remove them, a setting in the properties of the pivot table, this setting is disabled in this...
  20. L

    Paste data in an Excel table is very slow...

    "but then again, when there is nothing inside the table, there is no need for it to auto-extend." Yes you are right, but I just try to find a workaround, actually :DD But for me it is OK , in this case I just need to put a dummy data in A2, paste in A3, and finally remove line 2. This method...
  21. L

    Paste data in an Excel table is very slow...

    For information, I have tested the technic on other real cases and when the table to be refreshed : * is empty * has no formula the paste in A3 doesn't auto extend the table :(
  22. L

    Paste data in an Excel table is very slow...

    No problem i understand :) Yes I confirm, it the table is empty (1st line is header and second line is empty), then I paste in A2, it is very long. The only way to be fast is to paste in A3 (then remove line 2), as far as I can see.
  23. L

    Paste data in an Excel table is very slow...

    I know all of that of course :) But if i remove row 2 before and i copy passe in A2, Time of copy is long Again, since i m inside the table.
  24. L

    Paste data in an Excel table is very slow...

    Hello, For info in Excel 2016, behavior is stil the same if I copy in A2, very long So you're right, thank you ! It is fast by pasting it in A3, and the table auto extend. Remains the line 2 that it is useless So process is : paste in A3, then remove line 2 indeed. I find this very...
Back
Top