Forum Discussion
How to use a dataset Column without aggregation in a calculate function
- Anonymous1 year ago
Hi,SahityaYeruband .Thank you for your reply.
Like this?
select Month=1select Month =2
In order to exactly fit your given case data, I have modified the values of some Hits in the original data appropriately
This is the latest test data:Path
Hits
Month_Num
Sum_eachMonthHits
Sum_eachHits02
Index
A
5
1
6
9
1
A
1
1
6
9
2
B
12
1
20
26
3
B
8
1
20
26
4
C
1
1
2
18
5
C
1
1
2
18
6
A
3
2
3
9
7
B
6
2
6
26
8
C
10
2
16
18
9
C
6
2
16
18
10
I'm still using the two calculated columns I created, and I'm using their data to write a measure.Country
Dept
Path
US
Sales
A
US
HR
B
CN
IT
C
Here are the measures I created:M_All available values = VAR _count = CALCULATE ( DISTINCTCOUNT ( 'Logs'[Sum_eachHits02] ), FILTER ( 'Logs', 'Logs'[Sum_eachHits02] > 0 && 'Logs'[Sum_eachHits02] < 10 ) ) RETURN IF ( _count = BLANK (), 0, _count )hit_10 = CALCULATE( MAX('Logs'[Sum_eachMonthHits]),'Logs'[Sum_eachMonthHits]<10)Month_reportCount = VAR _countValues = CALCULATE ( DISTINCTCOUNT ( 'Logs'[Sum_eachMonthHits] ), 'Logs'[Sum_eachMonthHits] < 10 && 'Logs'[Sum_eachMonthHits] > 0 && 'Logs'[Sum_eachMonthHits] <> BLANK () ) RETURN IF ( _countValues = BLANK (), 0, _countValues )The reason for using IF judgement is to make the rows that originally had no value display as 0 instead of the original blank. in this case, since the relationship created will have the effect of field filtering, the system will ignore the rows that originally had no value to display by default. I use IF to determine if the current row's measure result is empty, and if it is empty, I assign a value of 0 to it.
I hope my test results can give you help.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Just to clarify, which of these 2 measures addresses the requirement :
Month_reportcount or M_all available units?
Month_reportcount works as expected - when month filter is selected, but it doesn't show correct values when no month filter is selected.
However, M_all available units shows correct values when no month values are selected, but the values are wrong when month filter is selected.
THanks,
Sahitya Y
Hi Anonymous ,
Thank you for the help. This was a new concept for me on how to handle measures using calculated columns.
I was able to make minor changes to get what I wanted -
I changed the below measures to countdistinct(path) instead of sum_hits
Month_reportCount =
VAR _countValues =
CALCULATE (
//DISTINCTCOUNT ( 'Logs'[Sum_eachMonthHits] ),
DISTINCTCOUNT ( Logs[Path] ),
'Logs'[Sum_eachMonthHits] < 10
&& 'Logs'[Sum_eachMonthHits] > 0
&& 'Logs'[Sum_eachMonthHits] <> BLANK ()
)
RETURN
IF ( _countValues = BLANK (), 0, _countValues )
&&
M_All available values =
VAR _count = CALCULATE(DISTINCTCOUNT(Logs[Path]),FILTER('Logs','Logs'[Sum_eachHits02]>0 && 'Logs'[Sum_eachHits02]<10))
RETURN IF(_count =BLANK(),0,_count)
and finally -
M_NotFilter = IF ( ISFILTERED ( Logs[Month_Num] ),
[Month_reportCount],
[M_All available values] )
as I asked in my previous reply which of the 2 measures to consider - Month_reportCount or M_All available values
The answer was to consider both 🙂
Thank you once again.
Sahitya Y