Forum Discussion

SahityaYeruband

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

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

11 Replies

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
    
    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.pbix31 KB
SahityaYeruband
Helper II
1 year ago
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
- Anonymous
  Not applicable
  1 year ago
  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
    Helper II
    1 year ago
    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