Forum Discussion

Helper II

1 year ago

Solved

How to use a dataset Column without aggregation in a calculate function

Hi All, I have 2 datasets - Logs: Path Hits Month_Num Master File: Country Dept Path Relationship - MasterFile(Path) to Logs(path) 1:M relationship I have...

column in calculate

Anonymous

1 year ago

Hi,SahityaYeruband .Thank you for your reply.
Like this?

select Month=1

select 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

Country	Dept	Path
US	Sales	A
US	HR	B
CN	IT	C

I'm still using the two calculated columns I created, and I'm using their data to write a measure.
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.

case01_11_27_follow.pbix34 KB

SahityaYeruband

Helper II

1 year ago

Hi Anonymous ,

Thank you for the detailed response.
However the expected output when no month is selected should be cummilative of all the available months.

Using the same sample data that you have used - when no moth value is selected the data should be :

Country	Dept	Hit_10	Report Count
CN	IT	-	0	3+16
US	HR		0	20+6
US	Sales		0	8+4

Thanks,

Sahitya Y

Anonymous

Not applicable

1 year ago

Hi,SahityaYeruband .Thank you for your reply.
Like this?
When the slicer does not select any value, the total number is calculated

Show filtered results when slicer is selected to filter data

I recreated a calculated column (grouping based on Path only, regardless of month)

Sum_eachHits02 = 
 VAR _path = [Path]
 RETURN
 CALCULATE(SUM('Logs'[Hits]),FILTER(ALL('Logs'),'Logs'[Path]= _path ))

Use the ISFILTERED function to determine whether the slicer field is filtered or not, and display the filtered result if it is being filtered, or display all the data of the corresponding Pash if it is not filtered (aggregation has already been implemented in the computed column [Sum_eachHits02]), so the only thing needed to call [Sum_eachHits02] in the measure is to Get the data for the current row (measure is still affected by the rows of table visual at this point)
The final result is as follows:

M_NotFilter = IF ( ISFILTERED ( Logs[Month_Num] ), 
    [hit_10],
     MAX ( 'Logs'[Sum_eachHits02] ) )

The results are as you would expect from the table data

Country	Dept	Hit_10	Report Count
CN	IT	-	0	3+16
US	HR		0	20+6
US	Sales		0	8+4

case01_11_27_follow.pbix31 KB

SahityaYeruband

Helper II

1 year ago

Hi Anonymous

Sorry for not being clear earlier.
Let me elaborate what is expected output -

Sample Input -

country	Dept	Path	Month Num	Hits
CN	IT	C	1	1
CN	IT	C	1	1
CN	IT	C	2	6
CN	IT	C	2	10
US	Sales	A	1	1
US	Sales	A	1	5
US	Sales	A	2	3
US	HR	B	1	8
US	HR	B	1	12
US	HR	B	2	6

Expected Output -
Filter (Month) = All available values
At Country Level -

Country	Count of reports with less than 10 hits
CN	0
US	1

At Country & Dept Level -

Country	Dept	Count of reports with less than 10 hits
CN	IT	0
US	Sales	1
US	HR	0

Filter (Month) = 1
At Country Level -

Country	Count of reports with less than 10 hits
CN	1
US	1

At Dept Level -

Country	Dept	Count of reports with less than 10 hits
CN	IT	1
US	Sales	1
US	HR	0

Filter (Month) = 2

At Country Level -

Country	Count of reports with less than 10 hits
CN	0
US	2

At Country & Dept Level -

Country	Dept	Count of reports with less than 10 hits
CN	IT	0
US	Sales	1
US	HR	1

Also reg the hits -
Its a derived column from User Name, so instead of hits my Logs table has - timestamp & user

When I try to create eachHits columns, I am getting circular dependency error with the Hits column.

THanks,

Sahitya Y

Anonymous

Not applicable

1 year ago

Hi,SahityaYeruband .Thank you for your reply.
Like this?

select Month=1

select 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

Country	Dept	Path
US	Sales	A
US	HR	B
CN	IT	C

I'm still using the two calculated columns I created, and I'm using their data to write a measure.
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 )

I hope my test results can give you help.

case01_11_27_follow.pbix34 KB

SahityaYeruband
Helper II
1 year ago
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

Forum Discussion

How to use a dataset Column without aggregation in a calculate function

Related content

calculate with max() function

Boolean Aggregate Functions?

Aggregate XIRR Calculation Help

SUM function doing wrongly calculation

performing filter and aggregate function on dataset before applying TOPN