Forum Discussion
Helper IIHow to use a dataset Column without aggregation in a calculate function
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.
Helper IIHi @v-jtian-msft ,
So the logic for Monthly works fine when only 1 month was selected, however, when multiple months are selected, it shows wrong values.
Any suggessions on how to modify the code to handle this?
Thanks,
Sahitya Y
Hi,SahityaYeruband .
You can create dummy tables in measure to implement the slicer multi-select effect.
like this.VAR _slicer=VALUES('Slicer_SellerID'[Seller ID])
//[Seller ID] is the field placed in the slicer (which acts as a pass-through value, so the slicer table and the original table don't need to have a relationship to avoid direct filtering)VAR _table=CALCULATETABLE(
VALUES(Sales[Client ID]),FILTER(
ALLSELECTED('Sales'),'Sales'[Seller ID] in _slicer)
)
//Sales[Client ID] is the field that really needs to be filtered. According to the slicer in the field filtered Sales [Client ID], the use of ALLSELECTED function to ensure that the measure will be affected by the slicer , here through the CALCULATETABLE function to create a virtual table
VAR result =IF(ISFILTERED(Slicer_SellerID[Seller ID]), IF(MAX('Sales'[Client ID]) IN _table, 1, 0),0)
RETURN result
// result is the final result of the measure. Indicates the final fields that need to be filtered out.Here is the complete code (you will need to modify the filtering logic of the code to suit your needs)
Using the CALCULATETABLE and VALUES functions generally solves the need for slicers to implement multiple selection
M_result = VAR _slicer=VALUES('Slicer_SellerID'[Seller ID]) VAR _table=CALCULATETABLE( VALUES(Sales[Client ID]),FILTER( ALLSELECTED('Sales'),'Sales'[Seller ID] in _slicer) ) VAR result =IF(ISFILTERED(Slicer_SellerID[Seller ID]), IF(MAX('Sales'[Client ID]) IN _table, 1, 0),0) RETURN result- SahityaYeruband
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
- SahityaYeruband
Hi Anonymous
Its not working as expected.
I have tried the said code as below :
Below are the results :