Forum Discussion

MarkPalmberg's avatar
MarkPalmberg
Kudo Commander
3 years ago
Solved

Tabular Editor Format String Expression help

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. I...
expression
Format
Tabular Editor
  • MarkPalmberg's avatar
    MarkPalmberg
    3 years ago

    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.

MarkPalmberg's avatar
MarkPalmberg
Kudo Commander
3 years ago

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....

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.