Power BI DAX IF not working exactly

[jibran]

Hi, 

I had a query where I want to match the values of credit side with the debit side based on ID.

I got help from one of the member to create formula, it checks the sum and give back the result. But on transaction level it is not matching the value. As shown below sum matches the value but on transaction level it still shows -1 i.e. Not Matched.

 

Equal = IF(CALCULATE(SUM(Query1[F_NOM_CREDIT]), ALLEXCEPT(Query1,Query1[F_NOM_VEH_ID],Query1[F_NOM_PERIOD]))=CALCULATE(SUM(Query1[F_NOM_DEBIT]), ALLEXCEPT(Query1,'Query1'[F_NOM_VEH_ID],Query1[F_NOM_PERIOD])),
	0,-1)

Some how I managed to filter them by the Vehicle ID and Nominal Period but still struggling to show as matched on transaction level.

Many Thanks 

[v-haibl-msft]

@jibran

Please try with following measure.

Equal2 =
IF (
    CALCULATE (
        SUM ( 'Query1'[F_NOM_CREDIT] ),
        ALLEXCEPT ( 'Query1', 'Query1'[F_NOM_VEH_ID] )
    )
        = CALCULATE (
            SUM ( 'Query1'[F_NOM_DEBIT] ),
            ALLEXCEPT ( 'Query1', 'Query1'[F_NOM_VEH_ID] )
        ),
    0,
    -1
)

Best Regards,
Herbert

[jibran]

Hi,

I tried but it is still doing the same thing as of before.

It is stil not matching on transaction level cost.

[mattbrice]

---

@jibran wrote:

Hi,

I tried but it is still doing the same thing as of before.

It is stil not matching on transaction level cost.

---

According to the screenshot above, Debits <> Credits, so @v-haibl-msft code of course will result in -1.

@jibran your initial calculated column results in -1 because if you group the subset of data by F_NOM_PERIOD, again Debits <> Credits so you will get -1.

[v-haibl-msft]

@jibran

What is the expected result for Equal2 measure in above table visual? If possible, could you please also provide your PBIX file to me? You can upload to online file service like OneDrive and share it to me.

Best Regards,
Herbert

[jibran]

Can I have your email address so I can share with you power BI report?

Many Thanks 

[v-haibl-msft]

@jibran

You can send to v-haibl@microsoft.com.

Best Regards,
Herbert

[jibran]

Hi Herbert,

I did send you the pbix file on your email address, did you recieved?

Many Thanks 

Jibran Ishtiaq

[v-haibl-msft]

@jibran

Could you please provide the expected result for each row in the table visual when selecting 1129? Do you want to compare the total DEBIT with the total CREDIT in the same PERIOD for same VEH_ID?

Best Regards,
Herbert

[jibran]

Hi Herbert,

Actullay the desired result is that to match total credit with total debit based on vehicle ID (Which has already been achieved with your help). But I want to match the Credit cost with debit cost at line level. So in case of 1129 "60.00" matches with debit side "60.00" and if matches it shows 0.

If this can not be achieved then can I put filter on equal2 formula (i.e. show me "0" or shown me "-1" only)

Many Thanks

Jibran Ishtiaq 

[v-haibl-msft]

@jibran

Could you please give me a sample in your PBIX which should return 0? In 1129, I do not see any equals as below.

Best Regards,
Herbert

[jibran]

Hi,

Thanks I'll try this now, just on a quick note how can I apply filter on 0,-1. That if I just want to see -1.

Many Thanks

Jibran