Forum Discussion

BI_Vijay's avatar
BI_Vijay
Regular Visitor
1 year ago

Need help with a DAX formula

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 valu...
Need Help
_AAndrade's avatar
_AAndrade
Resident Rockstar
1 year ago

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's avatar
BI_Vijay
Regular Visitor
1 year ago

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's avatar
    _AAndrade
    Resident Rockstar
    1 year ago

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

    • BI_Vijay's avatar
      BI_Vijay
      Regular Visitor
      1 year ago

      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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.