How to count records for different range including negative ?

[PNL]

Hi Community,

My Power Bi data model has 4 tables :

Feedback Records Master (Records all feedbacks with location, channel and rating details)

Channel Master (Stores different channels available at different locations. Not all channels are available at all locations)

Rating Master (Defines rating range and interpretation)

Location Master (Stores all available locations)

I need to write a measure or 2 separate measures to count no of countries with Poor feedback and Good feedback

Kindly help.

Thanks,

PNL

[daXtreme · Accepted solution]

@PNL 

Here's something you could adjust to your needs...

[# Good Feedback Countries] =
// If you want a measure for "Poor",
// change the FeedbackCategory variable
// and it'll work.
var FeedbackCategory = "Good"
var FeedbackMin = 
    calculate(
        selectedvalue( 'Rating Master'[Min] ),
        'Rating Master'[Feedback] = FeedbackCategory,
        removefilters( 'Rating Master' )
    )
var FeedbackMax = 
    calculate(
        selectedvalue( 'Rating Master'[Max] ),
        'Rating Master'[Feedback] = FeedbackCategory,
        removefilters( 'Rating Master' )
    )
var Output = 
    caclulate(
        distinctcount( 'Feedback Record Master'[Location] ),
        // If you want to be able to also slice by Rating
        // (this is not recommended), you should wrap the
        // two conditions in KEEPFILTERS, like:
        // keepfilters( 'Feedback Record Master'[Rating] >= FeedbackMin )
        // It's needed if you want to work within the current
        // filter context without overwriting it. If you hide
        // the Rating column (you should always hide your fact tables)
        // then you don't need this directive.
        'Feedback Record Master'[Rating] >= FeedbackMin,
        // adjust the boundaries accordingly in both condtions.
        'Feedback Record Master'[Rating] < FeedbackMax
    )
return
    Output

[daXtreme]

@PNL 

What would you want to see if you slice by country only and one country has a good feedback for one channel and a bad one for another? Please remember that measures should always work correctly for any slices in dimensions. Your definition of what you want to do is not clear enough for all possible slices. Please make more clear. For instance, a [Good Feedback] measure would then return 1 or 0 or... what? Maybe blank to inform somebody that there are countries with both types of feedback? Or maybe some other code?