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

Anonymous

Not applicable

1 year ago

Hi,SahityaYeruband .I am glad to help you.
Like this?

When the month is selected, data with Hits >0 and Hits <10 is displayed.

If you are having problems writing dax in Power BI Desktop, I hope my code below will help you.
This is my test data.

Measures:

hit_10 = 
 CALCULATE( MAX('Logs'[Sum_eachMonthHits]),'Logs'[Sum_eachMonthHits]<10)

reportCount = 
 CALCULATE( DISTINCTCOUNT('Logs'[Sum_eachMonthHits]),'Logs'[Sum_eachMonthHits]<10 &&'Logs'[Sum_eachMonthHits]> 0 && 'Logs'[Sum_eachMonthHits] <> BLANK() )

When you create relationships, there are problems with filtering when you use aggregate functions directly in measure, such as var _path =max(Path). Whereas in DAX, measure returns scalar values, so creating a calculation column grouping calculations is a better approach in this case.

If your issue is not occurring in power BI Desktop, but rather in Power BI Report Builder when creating a Data Set for a paged report, could you please provide more details, including the DAX to get the data source and relevant screenshots, which could help resolve your issue (you posted in the Report Server forums) the issue)
I think in this case you need to create a new data source table using the SUMMARIZECOLUMNS function
The dataset query code might look something like this.

EVALUATE
VAR _path =
    MAX ( 'Master File'[Path] )
VAR _table =
    SUMMARIZECOLUMNS (
        'Master File'[Path],
        'Master File'[Country],
        'Master File'[Dept],
        'Logs'[Month_Num],
        "Sum_eachMonthHits", CALCULATE ( SUM ( 'Logs'[Hits] ), GROUPBY ( 'Master File', [Path] ) )
    )
RETURN
    FILTER (
        _table,
        [Sum_eachMonthHits] < 10
            && [Sum_eachMonthHits] > 0
            && NOT ( ISBLANK ( [Sum_eachMonthHits] ) )
    )

Filter this datasetFilter with Month_Num as a query parameter

I have provided the test pbix file, hope it helps.

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.pbix30 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

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