Need help with a DAX formula

Question

I have 2 measuresone is Transactions&nbsp;- which is nothing but the Sum(Transactions) from my Fact 'Debit Transaction' and the other one is&nbsp;'Sector Transactions' - which actually should show the value for the whole sector of a customer.&nbsp;The measure is written in this way&nbsp;Sector Transactions =VAR SelectedSector = SELECTEDVALUE(Customer[Sector Name])VAR Output1 = &nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; ISBLANK(SelectedSector),&nbsp; &nbsp; &nbsp; &nbsp; BLANK(),&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM('Debit Transaction'[Transactions]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL(Customer), Customer[Sector Name]=SelectedSector&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )RETURN Output1&nbsp;&nbsp;This acutally works well.&nbsp;&nbsp;Now, if i take one more column from an another dimension then it just starts to show all the records from the other dimensions.&nbsp;&nbsp;For example: the other dimension is called - Contract and the column is called "Commercial Name" then it just shows all the values or records from the Contract Dimension. like this,&nbsp;&nbsp;&nbsp;But, the expected ourcome is,&nbsp;&nbsp;&nbsp;I basically want the CommercialName of the customer along witht the values to the measures but not all the commercialnames from the dimensioncan someone help on this?&nbsp;&nbsp;Thanks,Vijay

_aandrade · Answer

Hi&nbsp;BI_Vijay,
Please try this formula:
Sector Transactions =
VAR SelectedSector = SELECTEDVALUE(Customer[Sector Name])
VAR Output1 =
    IF(
        ISBLANK(SelectedSector),
        BLANK(),
        CALCULATE(
            SUM('Debit Transaction'[Transactions]),
            REMOVEFILTERS(Customer[Main Sector Name])  
        )
    )
RETURN Output1

bi_vijay · Answer

Thanks&nbsp;_AAndrade&nbsp;. I tried that, and it removed the filter on the Main Sector which is equivalent to the other measure "Transactions".&nbsp;This is how the output looks like&nbsp;&nbsp;&nbsp;

_aandrade · Answer

Could you please share a pbix file with an example of your data and data model?

bi_vijay · Answer

Hey&nbsp;_AAndrade&nbsp;, sorry i had other measures too in the visual which actually made the visuals to go wrong. I tried the same DAX from you again and i see the results are closer to what i want.&nbsp;and unfortunately, the PBIX has lot of PROD Tables - actually, 15 more which i haven't mentioned here - but they are perfectly connected.So, i'm trying to replicate the outputs in excel and posting it here.&nbsp;Thank you!

anonymous · Answer

Hi&nbsp;BI_Vijay&nbsp;，&nbsp;hello,_AAndrade, thank you for your prompt reply!&nbsp;
The core issue arises from a shift in the metric’s context.Please try the following measure to&nbsp; compare the result:
Sector Transactions =
VAR SelectedSector = SELECTEDVALUE(Customer[Sector Name])
VAR Output1 =
    IF(
        ISBLANK(SelectedSector),
        BLANK(),
        CALCULATE(
            SUM('Debit Transaction'[Transactions]),
            FILTER(
                ALL(Customer),
                Customer[Sector Name] = SelectedSector
            ),
            FILTER(
                VALUES(Contract[Commercial Name]),
                SUM('Debit Transaction'[Transactions]) &gt; 0 
            )
        )
    )
RETURN Output1

If the issue persists, please upload your sample table data include your relationships for further troubleshooting.
Best regards,
Joyce
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Need help with a DAX formula

6 Replies

Related content

If formula

formula

DAX Formula Help

DAX Formula

DAX Formula