Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

IF - OR Formula as a new measure

Hi,

 

I am looking to create a new measure in my power BI desktop that can help calculate the values from the table format I have stored as below - 

 

If Product name is Blank, value should be from the "total value" column for that unique id and if product name exists OR price available as TRUE, then take values separately from the "value" column for each unique ID.

 

 

 

Can someone help with a DAX formula please.

 

Unique idProduct NamePrice Available?ValueTotal Value
1BlankFALSE2040
1BlankFALSE2040
2ABCTRUE3065
2XYZTRUE3565
3ABC 2FALSE1010

 

Thanks!

4 REPLIES 4
Phil_Seamark
Employee
Employee

Hi @Anonymous

 

Can you please show your ideal expected result for that dataset.  It's not clear if you want to SUM values where more than 1 row qualify.

 

Cheers,

 

Phil


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Hi @Phil_Seamark - in the example I have given I woudl like to see the result as 115 but using the values in cells highlighted in red - i.e.  40+30+35+10 (cells highlighted in red).

 

Thanks,

Hi @Anonymous

 

This calculated measure might be close.  It generates 115  🙂

 

Measure =
    SUMX(
        DISTINCT('Table'),
        SWITCH(
            TRUE(),
            'Table'[Product Name]="Blank",[Total Value] ,
            'Table'[Product Name]<> "Blank"|| [Price Available?]=true,
            [Value],
            0
            )
            )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

Hi @Phil_Seamark,

 

Thank you for your reply. It did not seem to work 😞 ..

i get an error (red font under the comma) after i choose the column for the Distinct function. Not sure what im doing incorrect.

 

Can one not use the normal IF, THEN, ELSE nomenclature for DAX formula?

 

I'm trying to do another multiple IF function as a calculated field, where I want to state that if filter is done on one column with say condition A, B or C in table one, it should then calculate the targets as a separate calculation based on table 2.

 

if i filter on condition A in table 1, i would want target to be sum of total target (from table 2) / 4 (quarterly)

if i filter on condition B in table 1, i would want the target to be sum of total target (from table 2) / 12 (monthly)

 

Can you kindly advise how I can achieve variable targets based on filter on another table?

 

Thank you again!

Ash

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.