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 ,
I am not sure I can create that dummy slicer table.
below is how the tables are related :
Master file & Logs have to stay connected by the path (in my exact case, path+month_num).
How do I add the month slicer dummy table into this?
Thanks,
Sahitya Y
Hi Anonymous
Its not working as expected.
I have tried the said code as below :
Below are the results :