Custom visual with multiple DAX commands

swebb — Fri, 28 Apr 2023 17:18:02 GMT

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:

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

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.

Re: Custom visual with multiple DAX commands

amitchandak — Fri, 28 Apr 2023 20:59:57 GMT

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

Re: Custom visual with multiple DAX commands

swebb — Fri, 28 Apr 2023 21:06:03 GMT

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.

Re: Custom visual with multiple DAX commands

swebb — Tue, 09 May 2023 16:56:34 GMT

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.

topic Custom visual with multiple DAX commands in DAX Commands and Tips

Custom visual with multiple DAX commands

Re: Custom visual with multiple DAX commands

Re: Custom visual with multiple DAX commands

Re: Custom visual with multiple DAX commands