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...
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...
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...
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...
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...
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...
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...
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 :(
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]
)...
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...
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...
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...
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.
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...
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...
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...
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...
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...
"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...
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 :(
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.
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...