Reply
Highlighted
Regular Visitor
Posts: 41
Registered: ‎02-21-2017
Accepted Solution

How come it is aggregating?

[ Edited ]

I have requests that can be either service requests or information requests.

I have the following 3 measures to count them:

 

Nb Requests = CALCULATE(COUNT('311_Details'[DDS]))
Nb Service Requests = CALCULATE([Nb Requests];'311_Details'[Nature]<>"Information")
Nb Info Requests = CALCULATE([Nb Requests];'311_Details'[Nature]="Information")

How come the data now shows as aggregated, instead of getting broken down by Nature.

2017-03-17 16_29_26-311-dashboard - Power BI Desktop.png

The only change I made is to 

What coud explain such a a behaviour?

 

Thanks


Accepted Solutions
Super Contributor
Posts: 1,916
Registered: ‎08-11-2015

Re: How come it is aggregating?

[ Edited ]
Nb Info Requests =
CALCULATE (
    [Nb Requests];
    FILTER ( '311_Details'; '311_Details'[Nature] = "Information" )
)

Nb Service Requests =
CALCULATE (
    [Nb Requests];
    FILTER ( '311_Details'; '311_Details'[Nature] <> "Information" )
)

@osinquinvdm

The way you had originally written these they had an implied ALL function

FILTER ( ALL ( '311_Details'[Nature] )... => therefore ignoring any existing filter context!

http://www.sqlbi.com/articles/filter-arguments-in-calculate/

View solution in original post


All Replies
Super Contributor
Posts: 1,916
Registered: ‎08-11-2015

Re: How come it is aggregating?

Change the 1st measure to

Nb Requests = COUNT ( '311_Details'[DDS] )

 

Regular Visitor
Posts: 41
Registered: ‎02-21-2017

Re: How come it is aggregating?

sorry about that.

I just applied that simplification but as expected it does not make a difference.

How do I get NB DDI to show 0 on all lines except information?

Super Contributor
Posts: 1,916
Registered: ‎08-11-2015

Re: How come it is aggregating?

[ Edited ]
Nb Info Requests =
CALCULATE (
    [Nb Requests];
    FILTER ( '311_Details'; '311_Details'[Nature] = "Information" )
)

Nb Service Requests =
CALCULATE (
    [Nb Requests];
    FILTER ( '311_Details'; '311_Details'[Nature] <> "Information" )
)

@osinquinvdm

The way you had originally written these they had an implied ALL function

FILTER ( ALL ( '311_Details'[Nature] )... => therefore ignoring any existing filter context!

http://www.sqlbi.com/articles/filter-arguments-in-calculate/

Regular Visitor
Posts: 41
Registered: ‎02-21-2017

Re: How come it is aggregating?

Thank you so much.

I had no idea that not using an explicit FILTER() function would actually result in using an implicit ALL() function.

The small tweak you recommended made all the difference.

2017-03-20 14_12_03-311-dashboard - Power BI Desktop.png

Thanks again !