Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

DAX filtering help

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

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    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)
  • Baskar's avatar
    Baskar
    Resident Rockstar
    10 years ago

    Sorry i did't get u,

     

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

    my assumption 

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    10 years ago

    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,