Forum Discussion

jibran's avatar
jibran
Icon for Helper II rankHelper II
9 years ago
Solved

If statement with count

Hi Dev, I am trying to write a DAX query that shows me value of 0. if sum of debit side of a vehicle equals to the credit side of that period. My formula is below but it comes with an error Equal ...
Dashboards
Data Modeling
Reports
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi jibran,

     

    Please try to create a measure like below:

     

    Equal = IF(CALCULATE(SUM(Table1[F_NOM_ CREDIT]), ALLEXCEPT(Table1,'Table1'[F_NOM_VEH_ID],'Table1'[F_NOM_PERIOD]))=CALCULATE(SUM(Table1[F_NOM_DEBIT]), ALLEXCEPT(Table1,'Table1'[F_NOM_VEH_ID],Table1[F_NOM_PERIOD])),
	0,-1)

     

    Best Regards,
    Qiuyun Yu 

v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
9 years ago

Hi jibran,

 

Please try to create a measure like below:

 

Equal = IF(CALCULATE(SUM(Table1[F_NOM_ CREDIT]), ALLEXCEPT(Table1,'Table1'[F_NOM_VEH_ID],'Table1'[F_NOM_PERIOD]))=CALCULATE(SUM(Table1[F_NOM_DEBIT]), ALLEXCEPT(Table1,'Table1'[F_NOM_VEH_ID],Table1[F_NOM_PERIOD])),
	0,-1)

 

Best Regards,
Qiuyun Yu 

jibran's avatar
jibran
Icon for Helper II rankHelper II
9 years ago

Hi,

 

Thanks for the help I was away from the office it has worked, but can I use filter for example show me only -1 related data or show me 0 related data.

 

Many Thanks 

 

Jibran