Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Im working on a report where 2 Factual[IDMoment]'s are selected on the report filters, example 1000 and 1500.
From these IDs i have to show, for example, a Dimension Label1 in the rows, and some values coming from the DW, and then i need to calculate a Delta between the 2 IDs on the report.
So I use a matrix:
(1st of all: Is there any better visual to do this? Or a way to hide columns in Power BI?)
Im trying to control this using variables, to develop a good performing code and of course avoid doing unnecessary calculations
The idea is to avoid doing the calculation on delta2 of previous_idmoment "1000", and just showing blank () or zero
RESULT =
VAR SELECTED_2_IDs = ALLSELECTED(FACTUAL[IDMOMENT])
VAR LATEST_IDMOMENT = CALCULATE(MAX(FACTUAL[IDMOMENT]); FACTUAL[IDMOMENT] IN SELECTED_2_MOMENTS)
VAR PREVIOUS_IDMOMENT = CALCULATE(MIN(FACTUAL[IDMOMENT]); FACTUAL[IDMOMENT] IN SELECTED_2_MOMENTS)
RETURN IF (LATEST_IDMOMENT <> PREVIOUS_IDMOMENT;
...;
BLANK();)
This was working for a specific measure but i've done something and it stopped working....
Anyway, my question is what is the best way to control "where" calculations should be done and so, how to identify context of a column (or cell)
Thanks
Regards
Hi @Ricardo77 ,
Believe this can be done in the following way.
IDMOMENT = UNION(VALUES(FACTUAL[IDMOMENT]); {( "Delta")})
This create a table with all the IDMoments and an additional line with the value Delta
SUMOFVALUES =
VAR MAXIMUM =
MAX ( FACTUAL[IDMOMENT] )
VAR minimum =
MIN ( FACTUAL[IDMOMENT] )
RETURN
SWITCH (
SELECTEDVALUE ( IDMOMENT[IDMOMENT] );
"Delta";
CALCULATE (
SUM ( FACTUAL[DW] );
FILTER ( FACTUAL; FACTUAL[IDMOMENT] = MAXIMUM )
)
- CALCULATE (
SUM ( FACTUAL[DW] );
FILTER ( FACTUAL; FACTUAL[IDMOMENT] = minimum )
);
CALCULATE (
SUM ( FACTUAL[DW] );
FILTER ( FACTUAL; FACTUAL[IDMOMENT] = MAX ( IDMOMENT[IDMOMENT] ) )
)
)
As you can see below the column Delta is on the end of the data and no need to hide it on the several other columns, be aware that this solution has two limitations:
If you want to overcome those measures needs to be changed a little bit.
Check PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêshello @MFelix , what a great piece of DAX code, Jesus 😂!
I'll try it out and tell something after.
Please allow me just a few comments & outburts ("desabafos", as we say in portuguese):
1- DAX is not easy, this doesn't help end users (namely business users for self service BI...)
2- DAX must be carefully developed, or else is very slow, (so the perspective "let's try to see if this works" many times results in slow reports...)
I end with a question, related with the 2nd point:
is there anyway (considering your code or generally speaking) to refer a (previous) column in a table or matrix, to optimize calculations?
It could be like picking the delta you calculated, and using it in a complex calculation like
( row_columnA * row_columnB / row_columnZ) + (row_columnD *delta)
without having to calculate delta it again.
Thanks again, Regards.
Hi @Ricardo77 ,
Totally agree with you on the two points you refer, check this post from the great Alberto Ferrari where we defend that DAX is simple, but it is not easy.
https://www.sqlbi.com/blog/alberto/2020/06/20/7-reasons-dax-is-not-easy/
Regarding your question this is possible to have using the DELTA has a measure within a measure or making a temp_table. But if you could give some more information about the context that you need, because using measures depends on context also.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@Ricardo77 - You can use a custom matrix hierarchy to "hide" columns. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...
Not sure I am clear on requirements. Can you post sample data as text?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
50 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |