Power bi dax

[santoshyadav]

• I have two tables portfolio_dim and liquidity_fact which are joined with Id in model. Liquid table contains column like PortID, liquid_method, fraction, liquid_type. There are two types of liquid_method Sec and Int. One ID may have both the type of rows.

ID method fraction type

1, sec, 0.85, illiquid

1, Int, 0.75, illiquid 

2, Int, 0.65, illiquid 

If the ID with Sec method is also have ID with Int method the will show value of Sec and if not then value of Int.

I have to show the visual in desktop containing columns.

ID(dim table), type, fraction(sum)

1, illiquid, 0.85

2, illiquid, 0.65

[Wilson_]

Hi santoshyadav,

Try something like this as a measure:

Fraction (sum) =
VAR SecValue =
MAXX (
    FILTER (
        liquidity_fact,
        liquidity_fact[method] = "sec"
    ),
    liquidity_fact[fraction]
)
VAR Result = COALESCE ( SecValue, MIN ( liquidity_fact ) )

RETURN Result

I am assuming one ID has at most one sec row and one int row.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[quantumudit]

Hi @santoshyadav 

You can create a DAX measure like this and use it in your report table visual to get the desired result:

Fraction Value = 
VAR _secFraction =
    CALCULATE ( SUM ( Liquid_tbl[fraction] ), Liquid_tbl[method] = "sec" )
VAR _intFraction =
    CALCULATE ( SUM ( Liquid_tbl[fraction] ), Liquid_tbl[method] = "Int" )
RETURN
    IF ( ISBLANK ( _secFraction ), _intFraction, _secFraction )

You can also create a small measure "Total Fraction" as follows, and use it instead of SUM ( Liquid_tbl[fraction] )

Total Fraction = SUM ( Liquid_tbl[fraction] )

Here is the screenshot of the solution:

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree