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
pmadam
Helper II
Helper II

DAX HELP!!

Hi Team,

 

Please help with below request.

 

I  have 3 measures and 3 dimensions as shown below.

 

Dims                                 Measure

Branch Budget                 Budget

InsideSalesName              InsideSalesBudget

    ->Inside SalesNumber

OusideSalesName         OutsideSalesBudget

   ->OutsideSalesNumber

 

So i need to create single  calculated measure 'Budget' that displays respective measure value depending on dimension picked by user in visual.

 

We we having this in multi-dimensional like below but i need in DAX

 

SCOPE([MEASURES].[Budget]);
SCOPE([Outside Sales].[Outside Sales].[Outside Sales Name].MEMBERS);
IF NOT ISEMPTY([MEASURES].[Ship Resale - SALE]) OR NOT ISEMPTY([MEASURES].[Book Resale - SALE]) THEN THIS = [MEASURES].[Outside Sales Budget]
END IF;
IF ISEMPTY([MEASURES].[Ship Resale - SALE]) AND ISEMPTY([MEASURES].[Book Resale - SALE]) THEN THIS = NULL
END IF;
END SCOPE;
SCOPE([Outside Sales].[Outside Sales].[Outside Sales Number].MEMBERS);
IF NOT ISEMPTY([MEASURES].[Ship Resale - SALE]) OR NOT ISEMPTY([MEASURES].[Book Resale - SALE]) THEN THIS = [MEASURES].[Outside Sales Budget]
END IF;
IF ISEMPTY([MEASURES].[Ship Resale - SALE]) AND ISEMPTY([MEASURES].[Book Resale - SALE]) THEN THIS = NULL
END IF;
END SCOPE;
END SCOPE;

 

 

1 REPLY 1
amitchandak
Super User
Super User

@pmadam ,

Now if you want change both dimension(Axis/Row/Group by) and measure, You need bookamarks and button

 

Dynamically change chart axis in Power BI
bookmark -https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/

 

But if only want change the axis then you need to have independent table with these three values and use a measure slicer 

 

Measure  =
var _change =selectedvalues(Table[Dimension])
return
SWITCH (
TRUE(),
_change = "Branch Budget " , [Budget ],
_change = "Inside Sales Name" , [InsideSalesBudget],
_change = "Outside Sales Budget " , [OutsideSalesBudget],
)

 

measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...

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.