Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 = IF(Sum(Query1[F_NOM_DEBIT]) = SUM(Query1[F_NOM_CREDIT]), Format(0,ALLEXCEPT(Query1,Query1[F_NOM_VEH_ID],Query1[F_NOM_PERIOD])), -1)
But it comes with error "The expression refers to multiple columns. Multiple columns cannot be coverted to a scalar value"
So where it matches the total value of a specific ID example 10157 it should show 0 and where not it should show -1
Many Thanks
Jibran
Solved! Go to Solution.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |