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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
swebb
Helper II
Helper II

Custom visual with multiple DAX commands

All,

 

I have a custom financial report structure with measures for each entry. One of the issues I'm running in to is I can't update some of the fields, and for the life of me I cannot figure out why. I can update other fields just fine. See below for screen shots.

 
Individual DAX measures:
Working:
Subscription = SUMX(
    FILTER(
        'Consolidated',
        'Consolidated'[accountNumber] IN {410000}
    ),
    'Consolidated'[TotalExchangeRateAmount]
)
 
Not working:
EBITDA Interest = SUMX(
    FILTER(
        'Consolidated',
        'Consolidated'[accountNumber] IN {730000, 740000, 740010}
    ),
    'Consolidated'[TotalExchangeRateAmount]
)
 
For the Structure of the Report:
This gets applied to the Matrix visualization value field
Financial Value =
SWITCH(
    SELECTEDVALUE('Structure'[Main Struct - Copy]),
    "Subscription", FORMAT([Subscription], "Currency"),
    "Maintenance", FORMAT([Maintenance], "Currency"),
    "Total Recurring", FORMAT([Total Recurring], "Currency"),
    "License", FORMAT([License], "Currency"),
    "Services", FORMAT([Services], "Currency"),
    "Other Rev.", FORMAT([Other Rev.], "Currency"),
    "People", FORMAT([People], "Currency"),
    "Salary", FORMAT([Salary], "Currency"),
    "Taxes and Benefits", FORMAT([Taxes and Benefits], "Currency"),
    "Commission", FORMAT([Commission], "Currency"),
    "Bonus", FORMAT([Bonus], "Currency"),
    "Automobile", FORMAT([Automobile], "Currency"),
    "Other People", FORMAT([Other People], "Currency"),
    "Recruiting", FORMAT([Recruiting], "Currency"),
    "Contract Services", FORMAT([Contract Services], "Currency"),
    "Microsoft ISV", FORMAT([Microsoft ISV], "Currency"),
    "Consulting", FORMAT([Consulting], "Currency"),
    "Commission - Partners", FORMAT([Commission - Partners], "Currency"),
    "Trade Show", FORMAT([Trade Show], "Currency"),
    "User Conference", FORMAT([User Conference], "Currency"),
    "Hosting", FORMAT([Hosting], "Currency"),
    "Travel & Ent.", FORMAT([Travel & Ent], "Currency"),
    "Office", FORMAT([Office], "Currency"),
    "Technology", FORMAT([Technology], "Currency"),
    "Computer", FORMAT([Computer], "Currency"),
    "Software", FORMAT([Software], "Currency"),
    "Dues and Subscr.", FORMAT([Dues and Subscrip], "Currency"),
    "Other Technology", FORMAT([Other Technology], "Currency"),
    "Accounting", FORMAT([Accounting], "Currency"),
    "Transaction", FORMAT([Transaction], "Currency"),
    "Marketing", FORMAT([Marketing], "Currency"),
    "Professional Services", FORMAT([Professional Services], "Currency"),
    "Intercompany Mgmt. Fee", FORMAT([Intercompany Mgmt. Fee], "Currency"),
    "Other", FORMAT([Other], "Currency"),
    "Misc.", FORMAT([Misc.], "Currency"),
    "Interest", FORMAT([Interest], "Currency"),
    "Taxes", FORMAT([Taxes], "Currency"),
    "Amortization", FORMAT([Amortization], "Currency"),
    "Depreciation", FORMAT([Depreciation], "Currency"),
    "Gain/Loss on Exchange", FORMAT([Gain/Loss on Exchange], "Currency"),
    "Intercompany", FORMAT([Intercompany], "Currency"),
    "Total Expenses", FORMAT([Total Expenses], "Currency"),
    "Net Operating Income", FORMAT([Net Operating Income], "Currency"),
    "Total Revenue", FORMAT([Total Revenue], "Currency"),
    "EBITDA Interest", FORMAT([EBITDA Interest], "Currency"),
    "EBITDA Taxes", FORMAT([EBITDA Taxes], "Currency"),
    "EBITDA Depreciation", FORMAT([EBITDA Depreciation], "Currency"),
    "EBITDA Amortization", FORMAT([EBITDA Amortization], "Currency"),
    "EBITDA", FORMAT([EBITDA], "Currency"),
    "Plus:Addbacks", FORMAT([Plus: Addbacks], "Currency"),
    "Plus:Intercompany", FORMAT([Plus: Intercompany], "Currency"),
    "Plus:Transaction", FORMAT([Plus: Transaction], "Currency"),
    "Plus:Gain/Loss on Exchange", FORMAT([Plus: Gain/Loss on Exchange], "Currency"),
    "Adj. EBITDA", FORMAT([Adj. EBITDA], "Currency"),
    "Less Capitalized Commissions", FORMAT([Less Capitalized Commissions], "Currency"),
    "Less Capex", FORMAT([Less Capex], "Currency"),
    "Less Capitalized Software", FORMAT([Less Capitalized Software], "Currency"),
    "FCF", FORMAT([FCF], "Currency")
    )
   
Before and after changing the DAX to one of the non-working value fields:
swebb_0-1682702170775.png

 

swebb_1-1682702192936.png


When trying to apply the DAX measure to the EBITDA Interest I just get blanks across all:

swebb_2-1682702212866.png

 

 I verified the table data actually has data in the TotalExchangeRateAmount column, and that there are corresponding dates for all the months, or at least some of the months. Just not sure what I'm doing wrong. I can provide additional information if needed. 
3 REPLIES 3
swebb
Helper II
Helper II

@Greg_Deckler Any thoughts on this? I can provide additional information as needed. TL;DR, have a custom formatted Matrix visualization with DAX measures for each entry, most of them work, some of them do not. Checked formatting, checked values in corresponding table, all seems to look good, but cannot get values to populate in any of the blank rows now. 

amitchandak
Super User
Super User

@swebb , The measure seems fine to me, unless there is some issue with spaces or special characters.

Just create a small measure for those values for any such issue.

 

Also, when everything is formatted to currency, Why are you formatting and creating a text measure, you can format it once using measure tools

The values were showing up wonky when on the matrix visualization, even though I limited the amount of decimal values. I went through and did the FORMAT and it corrected it. Not really sure why. 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.