Forum Discussion

sravan872's avatar
sravan872
Frequent Visitor
1 year ago
Solved

Need help in dax

1. I have a consolidated portfolio table which has fund, security, port wt, esg flag, si flag, asset class, path etc. Now i need to calculate sum(port wt) with filters as security type in (abs,cmbs,m...
DAX
power bi
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi sravan872 
    Thank you for reaching out to the Microsoft fabric community forum. Thank you FBergamaschi  for your inputs on this issue.

    After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.     

    I am also including .pbix file for your better understanding, please have a look into it:

    If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

    Thank you for using Microsoft Community Forum.

sravan872's avatar
sravan872
Frequent Visitor
1 year ago

Thanks for the reply FBergamaschi. Please find below

 

In this table, I need a new measure to calculate E/S port wt. The logic need is

ES2 =
CALCULATE(SUM(fund[PORT WT]), FILTER(VW_CONSOLIDATED_PORTFOLIO_ESG_SNAPSHOT,
NOT VW_CONSOLIDATED_PORTFOLIO_ESG_SNAPSHOT[SECURITY_TYPE] IN {"ABS", "CMBS", "MBS"} &&
 FUND[PATH_NTGI_ADDCHAR] <> "BACKED")). 
To this measure i want to add another filter for 4 specific funds B,C,D & E if asset class is SOV and si flag is 1 then include that row in the sum(port wt), if asset class is sov and si flag is 0 exclude that row from sum(port wt). this logic is only for these 4 funds. Can you help in this please. Let me know if any further details are required?
Thanks.
FBergamaschi's avatar
FBergamaschi
Icon for Super User rankSuper User
1 year ago

My code

 

ES2 =

CALCULATE (

    SUM ( 'PowerBI_Sample_Portfolio'[PORT WT] ),

    FILTER (

        'PowerBI_Sample_Portfolio',

        NOT 'PowerBI_Sample_Portfolio'[SECURITY TYPE] IN { "ABS", "CMBS", "MBS" }

                && 'PowerBI_Sample_Portfolio'[PATH] <> "BACKED"

                &&

                        NOT 'PowerBI_Sample_Portfolio'[FUND] IN { "B", "C", "D", "E" }

                    ||

                        (
                          'PowerBI_Sample_Portfolio'[FUND] IN { "B", "C", "D", "E" }

                           &&
                              (
                                 'PowerBI_Sample_Portfolio'[ASSET CLASS] = "SOV"

                                   && 'PowerBI_Sample_Portfolio'[SI FLAG] = 1
                              )
                        )

    )

)

 

my code is a bit different than the one from v-tsaipranay, let us know if either of the two understood fully your request

 

Pls mark as kudos and solution in case my answer or v-tsaipranay one!

 

Best

FB