DAX filtering help

[Anonymous]

I have some simple data with slicers on CUSTOMER, YEAR, and MONTH that just shows a summary table of their monthly charges. The wrinkle is that for a service there may be a negotiated monthly charge with a MONTH value of 0 that supercededs any actual CHARGES for any month

Customer	Service	Year	Month	Charges
CustA		X	2016	0	500
CustA		X	2016	1	120
CustA		X	2016	1	330
CustA		X	2016	1	200
CustA		X	2016	2	125

So if slicing the charges for 2016, 01 for this customer it would be 500 regardless of how much or little they actually used.

I'm wondering if I need a new calculated column that does a form of a coalesce

pseudocode:

coalesce( (service=x & value=0), sum(service=x & charges) )

Its a little ambiguous but if you have any thoughts I'd be happy to try them out

[v-sihou-msft]

@Anonymous

In your scenario, this negotiated monthly charge is based on number of rows for Month 0, or the charge value for Month 0?

For first scenario, you measure can be like:

Mesure= IF(
CALCULATE(COUNTROWS('Table'),'Table'[Month]='0')>0,
CALCULATE(SUM('Table'[Charge]),'Table'[Month]='0'),
CALCULATE(SUM('Table'[Charge]))
)

For sceond scenario, your measure can be like:

Mesure= IF(
CALCULATE(SUM('Table'[Charge]),'Table'[Month]='0')>0,
CALCULATE(SUM('Table'[Charge]),'Table'[Month]='0'),
CALCULATE(SUM('Table'[Charge])))

Regards,

[Baskar]

Sorry i did't get u,

Do u want a new filed with some condition am i rigth ? 

my assumption 

[Greg_Deckler]

I would recommend using a measure, something like:

Measure = IF(CALCULATE(COUNTROWS('Table'),'Table'[Month]=0)>0,True Condition with 0 month value,False Condition with standard value)