Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have the following data model:
Date | Dimension 1 | Dimension 2 | Count | Count unique |
---|---|---|---|---|
January | A | 10 | 8 | |
January | A | B | 12 | 9 |
January | B | 2 | 1 | |
February | A | 12 | 8 | |
February | A | B | 17 | 13 |
February | B | 5 | 3 |
I have Filters in the model for the dimensions and an expression on sum (count) and sum (count unique).
the issue is, when I choose a filter only on dimension1 = A on January I want to get the values only from the row where I have only A, and exclude from the expression the row with both A and B.
how can I do that?
Thanks,
Boris
Can you please explain what you did here?
could you please add the same solution if the date is also a dimension?
and how Can I do it for the large amount of dimension meta data that I have?
Hi,
The idea is to display any dimension when -
1. All Other dimensions are not selected ( Initial condition with no selection, this will display all dimension initially)
2. Show Dimensions When value in this dimension is selected, hide it when other dimensions are selected and this one is not selected.
So we will have (Dim1 AND Dim2 AND Dim3) OR Dim4 kind of condition in each 'enable condition' expression of each dimension. The AND section to check other dimensions are not selected and after OR to check if the dimension where we are putting this condition is selected.
PFA the attached updated file with Date also shown/hidden based on its selection properties.
I think this solution is not based on individual values of dimensions, the condition will expand or shrink based on # of dimensions we have.
Is this you wanted? Let me know if any improvement is needed?
try loading this csv.
I added 2 rows where I have only the a date.
in our aggregations when only a date is select it should show only this row, not the sum on all the rows who have this date.
also when I select the a certain date and a certain dimension it should only show me the relevant row.
Date | Dimension 1 | Dimension 2 | Count | Count unique |
January | A | 10 | 8 | |
January | A | B | 12 | 9 |
January | B | 2 | 1 | |
February | A | 12 | 8 | |
February | A | B | 17 | 13 |
February | B | 5 | 3 | |
February | 20 | 17 | ||
January | 24 | 16 |
I understand from this is that you always want to consider Date, Dim1, Dim2 to group your count and count unique. Since the conditions were put in the dimension - enable condition attribute, it was ignoring the not selected dimension and grouping measures based on selected dimensions. Now I have moved all the conditions to show/hide condition in presentation tab. See if this behaviour is close to your requirement or not.
almost, but I need the measure from the row that I have only the month in it? how do I narrow it to this point?
You are right, it is showing either extra records or grouped records. I am not finding any possibility of only setting up dimensions and achieving the requirement.
But I found the set expression condition which is generic for each expression.
Sum({<Date={'$(=GetFieldSelections(Date))'},[Dimension 1]={'$(=GetFieldSelections([Dimension 1]))'},[Dimension 2]={'$(=GetFieldSelections([Dimension 2]))'}>}Count)
I had to change nulls to '-' for proper comparison in the script.
Script added in preceding load as -
Load if(Len(Date)=0,'-',Date) as Date,
if(Len([Dimension 1])=0,'-',[Dimension 1]) as [Dimension 1],
if(Len([Dimension 2])=0,'-',[Dimension 2]) as [Dimension 2],
Count,
[Count unique];
But now I think its working as desired