topic Re: Tabular Editor Format String Expression help in DAX Commands and Tips

Tabular Editor Format String Expression help

MarkPalmberg — Thu, 08 Dec 2022 18:42:08 GMT

Hi.

I'm new to Tabular Editor and calculation groups, and I'm wondering if I might get some advice.

I have a table containing a dimension that controls dynamic measure selection in my PBIX. It looks like this:

IF(ISCROSSFILTERED('Measure Dimensions'[Dimension]), SWITCH(TRUE(), VALUES('Measure Dimensions'[Dimension]) = "Accepted Sum",[Accepted Amount], VALUES('Measure Dimensions'[Dimension]) = "Expected Sum",[Expected Ask Amount], VALUES('Measure Dimensions'[Dimension]) = "Ask Sum",[Ask Amount], VALUES('Measure Dimensions'[Dimension]) = "Accepted Count",[Accepted Ask Count], VALUES('Measure Dimensions'[Dimension]) = "Expected Count",[Expected Ask Count], VALUES('Measure Dimensions'[Dimension]) = "Ask Count",[Asks Made Count], BLANK() ),BLANK() )

I followed some online tutorials to build out a Time Intelligence calculation group in Tabular Editor (TE). One of my takeaways there was that the format set for the calculation items in TE overrides the format set on the measure in the PBIX; this is fine given my current use case.

Where I'm at now is setting the format for the SELECTEDMEASURE() based on the name of the selected measure from my measure dimension table. So, if it's a count, the format should be (I think) "0,0;-0,0", and if it's a sum, it should be "$0.00;-$0.00".

Can I accomplish this via the Format String Expression in the Expression Editor in TE? If so, do I have to explicitly add the same DAX to the Format Expression for each of my calculation items?

Thanks for any tips you can pass long!

Re: Tabular Editor Format String Expression help

MarkPalmberg — Thu, 08 Dec 2022 21:19:48 GMT

So, I'm zeroing in(?). Here's what I've entered in the Format String Expression field for my "Current" Time Intelligence calculation item:

VAR DOLLARS = SWITCH( TRUE(), SELECTEDMEASURENAME() = "Accepted Sum",1, SELECTEDMEASURENAME() = "Ask Sum",1, 0 ) VAR SumFormat = ( "$#,0.00" ) VAR CountFormat = ( "#,0") RETURN IF (DOLLARS,SumFormat,CountFormat)

And here's what it now looks like in my matrix when I select one of my "sum" measures:

So the comma is working, at least; dunno yet why the $ isn't displaying....

Re: Tabular Editor Format String Expression help

Anonymous — Fri, 09 Dec 2022 02:02:28 GMT

Hi MarkPalmberg ,

Please have a try.

My sample measure.

Measure = IF(MAX('Table'[Column1])>20,FORMAT(MAX('Table'[Column1]),"$#,0.00"),FORMAT(MAX('Table'[Column1]),"#,0"))

You can change you measure.

VAR DOLLARS = SWITCH( TRUE(), SELECTEDMEASURENAME() = "Accepted Sum",1, SELECTEDMEASURENAME() = "Ask Sum",1, 0 ) VAR SumFormat = FORMAT(MAX('Table'[Column]),"$#,0.00") VAR CountFormat = FORMAT(MAX('Table'[Column1]),"#,0") RETURN IF (DOLLARS,SumFormat,CountFormat)

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Tabular Editor Format String Expression help

MarkPalmberg — Fri, 09 Dec 2022 15:21:17 GMT

Thank you for the reply, Anonymous . I think I did a poor job of explaining my desired final result; I apologize. It's a matrix visual and accompanying measure slicer. The measure slicer displays the values in the unrelated Measure Dimensions table in my PBIX:

Measure Dimensions = { ("Accepted Sum",1), ("Ask Sum",2), ("Expected Sum",3), ("Accepted Count",4), ("Ask Count",5), ("Expected Count",6) }

Which contains, in turn, a measure called Measure Selection:

Measure Selection = IF(ISCROSSFILTERED('Measure Dimensions'[Dimension]), SWITCH(TRUE(), VALUES('Measure Dimensions'[Dimension]) = "Accepted Sum",[Accepted Amount], VALUES('Measure Dimensions'[Dimension]) = "Expected Sum",[Expected Ask Amount], VALUES('Measure Dimensions'[Dimension]) = "Ask Sum",[Ask Amount], VALUES('Measure Dimensions'[Dimension]) = "Accepted Count",[Accepted Ask Count], VALUES('Measure Dimensions'[Dimension]) = "Expected Count",[Expected Ask Count], VALUES('Measure Dimensions'[Dimension]) = "Ask Count",[Asks Made Count], BLANK() ),BLANK() )

Each of the measures referenced there is from my main Measures table in my PBIX. I created a Time Intelligence calculation group with a couple calcuation items that I'd like to display in my matrix. When I select a measure in my slicer that's a count, I want the format to be "#,0". When I select a measure that's a sum, I want the format to be "$#,#.00".

So when I make this selection, I expect to see whole numbers with a comma (where applicable):

When I make this selection, I'd like to see floats out to 2 digits and a dollar sign:

FWIW, the YoY% format is set via the Format String Expression in the YoY% calculation item in Tabular Editor:

I've tried setting the Format String Expression for the "Current" calculation item in Tabular Editor thusly:

VAR DOLLARS = SWITCH( TRUE(), SELECTEDMEASURENAME() = "Accepted Sum",1, SELECTEDMEASURENAME() = "Ask Sum",1, 0 ) //VAR SumFormat = ( "$#,#.00") --excluded for testing //VAR CountFormat = ( "(#,0)") --excluded for testing RETURN IF (DOLLARS,"$#,#.00","#,0")

and this expression does seem to be applying the comma (I made sure the settings for the measure in the PBIX are set to not display a comma), but it's not applying the $ sign.

I was looking at this yesterday afternoon and wonder if my resolution is that I need to create another calculation group in Tabular Editor to house my measures, then use those measures in my matrix. I've only read through that article 5 times now, so I'll need some time to read it five more times, which is the average number of times I need to read a SQLBI article before I understand it. 🤣

Thanks again for taking the time to look at this issue.

Re: Tabular Editor Format String Expression help

MarkPalmberg — Fri, 09 Dec 2022 18:08:46 GMT

Well, the (a) solution appears to be to not use SELECTEDMEASURENAME() in the Format String Expression box but to use the SELECTEDVALUE() from my Measure Dimensions table. Like:

VAR DOLLARS = SWITCH( TRUE(), SELECTEDVALUE('Measure Dimensions'[Dimension]) = "Accepted Sum",1, SELECTEDVALUE('Measure Dimensions'[Dimension]) = "Ask Sum",1, 0 ) VAR SumFormat = ( "$#,#.00") VAR CountFormat = ( "(#,0)") RETURN IF (DOLLARS,SumFormat,CountFormat)

Thanks again for your time looking at this, Anonymous ! I really appreciate it and am sorry to have wasted it!

EDIT: Here's the post that finally got through my thick skull.