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.
Hi there,
I am building a report to represent the cumulative of a column (the cashflow) over time (different years) based on some categories (i.e. different asset_types). The problem is that not all the years have an equivalent value and consequently these years are shown blank in my bar chart. I was wondering if someone let me know how to fix my DAX query.
@Anonymous
Hi there, thanks for the link, I never thought these issues can be fixed with an appropriate data model. I was always seeing these concepts in the field of data model not DAX queries. The link looks very interesting, I will go through this (and the if clause) over the next few days and report back.
Hi @Anonymous ,
Thanks again for the explanation and also the link. It was very educational,.
I have formed a star schema by breaking my big table into smaller ones by some DAX expressions such as
Dim_Asset_type = DISTINCT(Output_SSIS[Asset Type])
So the whole * schema is shown below:
I put some filtering options below my chart which are coming from these dimensions and I use them to filter the chart values.
But still do not know how to modify my cumulative and total value (also a dax expression) to use these filters. The Y axis shows incorrect values, repetetive values, I guess it is because of the "allselected" function in the dax, please see below
Now my cumulative is :
I think I have fixed that, finally !!, I had to put values at the end, so the code is :
@Anonymous ,
Try like
CALCULATE (sum (Output_SSIS [Cash Flow]),
filter(
ALLselected (Output_SSIS),
Output_SSIS [Financial Year] <= Max (Output_SSIS [Financial Year])
)
)
or
CALCULATE (sum (Output_SSIS [Cash Flow]),
filter(
ALLselected (Output_SSIS),
Output_SSIS [Financial Year] <= Max (Output_SSIS [Financial Year]) && Output_SSIS[Asset type] = max(Output_SSIS[Asset type])
)
)
Thanks Amit, but none of them works
and the second one
I think I need to modify the calculation somehow to force it to use Output_SSIS [Financial Year]-1 if isblanck(Output_SSIS [Financial Year]) with some if statements possibly with some variables, but not sure exactly how but working on it
User | Count |
---|---|
42 | |
20 | |
19 | |
17 | |
14 |
User | Count |
---|---|
54 | |
19 | |
18 | |
17 | |
15 |