cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jibran Regular Visitor
Regular Visitor

Power BI DAX IF not working exactly

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.

 pi and si v1.JPG

 

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 

 

 

11 REPLIES 11
Microsoft v-haibl-msft
Microsoft

Re: Power BI DAX IF not working exactly

@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 Regular Visitor
Regular Visitor

Re: Power BI DAX IF not working exactly

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 

jibran Regular Visitor
Regular Visitor

Re: Power BI DAX IF not working exactly

Hi,

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

It is stil not matching on transaction level cost.

pi and si v2.JPG

Microsoft v-haibl-msft
Microsoft

Re: Power BI DAX IF not working exactly

@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

mattbrice Senior Member
Senior Member

Re: Power BI DAX IF not working exactly


@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.

pi and si v2.JPG

 


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.

 

 

jibran Regular Visitor
Regular Visitor

Re: Power BI DAX IF not working exactly

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

 

Many Thanks 

Microsoft v-haibl-msft
Microsoft

Re: Power BI DAX IF not working exactly

@jibran

 

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

 

Best Regards,
Herbert

jibran Regular Visitor
Regular Visitor

Re: Power BI DAX IF not working exactly

Hi Herbert,

 

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

 

Many Thanks 

Jibran Ishtiaq

Microsoft v-haibl-msft
Microsoft

Re: Power BI DAX IF not working exactly

@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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,287)