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

[SahityaYeruband]

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 to create a measure that will give the count of reports whose hits is less than 10 - by dept & country, based on the month filter.

I have tried to take calculate(sum(hits), path = _path) but for this the var _path needs to be defined using aggregate function in the measure which doesn't work as expected.

I have to build a dax for the below sql - 
select country, dept, count(report) from 
(select country, dept, path, sum(hits) from <tables>
where month in <selected filter values>
group by country, dept, report having sum(hits) > 0 and sum(hits) < 10 and sum(hits) is not null)
group by country,dept

I am  stuck with this for some time now, appreciate any help to solve this issue.

Thanks in advance

[SahityaYeruband]

Hi @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

[v-jtian-msft]

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 @v-jtian-msft ,

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 @v-jtian-msft 
Its not working as expected.
I have tried the said code as below : 

Below are the results : 

[v-jtian-msft]

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.

[SahityaYeruband]

Hi @v-jtian-msft ,

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

[v-jtian-msft]

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

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.

[SahityaYeruband]

Hi @v-jtian-msft 

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

[v-jtian-msft · Accepted solution]

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.

[SahityaYeruband]

Hi @v-jtian-msft ,

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

[SahityaYeruband]

Hi @v-jtian-msft ,

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