Need help with a DAX formula

[BI_Vijay]

I have 2 measures

one is Transactions - which is nothing but the Sum(Transactions) from my Fact 'Debit Transaction' and the other one is

 'Sector Transactions' - which actually should show the value for the whole sector of a customer.

The measure is written in this way

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

            )

        )

    )

RETURN Output1

This acutally works well. 

Now, if i take one more column from an another dimension then it just starts to show all the records from the other dimensions. 

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,

 But, the expected ourcome is, 

I basically want the CommercialName of the customer along witht the values to the measures but not all the commercialnames from the dimension

can someone help on this?

Thanks,

Vijay

[_AAndrade]

Hi @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]

Thanks @_AAndrade . I tried that, and it removed the filter on the Main Sector which is equivalent to the other measure "Transactions". 

This is how the output looks like

[_AAndrade]

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

[BI_Vijay]

Hey @_AAndrade , 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.

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.

Thank you!

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[Anonymous]

Hi @BI_Vijay ， hello,_AAndrade, thank you for your prompt reply!
 

The core issue arises from a shift in the metric’s context.

Please try the following measure to  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]) > 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 helps, then please consider Accept it as the solution to help the other members find it more quickly.