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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Merge measure for text display with dynamic visuals

Hi all,

following issue: 

I can change what`s displayed in my visual via a slicer (Cont of Documents, Euro Value, Local Value).

I am now required to display what Unit is used but in the visual itself! So I can't just make a card somewhere in the page where I display it, is has to be displayed in the visual itself (so it doesn't disappear when you apply Focus mode onto a visual).

 

I tried it using the following function:

 

Amount Sales Docs& Net Value =
SWITCH(TRUE(),
VALUES('Measure Dimensions'[Measure])= "Count", CONCATENATE([Unit Display],[C Amount of Sales Orders]),
VALUES('Measure Dimensions'[Measure])= "Local Currency", CONCATENATE([Unit Display], [LC Net Value]),
Values('Measure Dimensions'[Measure])= "Euro Value", CONCATENATE([Unit Display],[€ Net Value]),
BLANK())
 
For this a few hints:
The measure "Unit Display" already successfully shows me the displayed Unit when I apply it onto a card.
The other measures work just fine as well
The whole functions also works if I remove the "CONCATENATE([Unit Display]),", just when I try to integrate it, it won't work and shows "No data to be displayed" 
ANyone an idea for a workaround? Ideally where you can determine the format and position of the text ?
12 REPLIES 12
Anonymous
Not applicable

Dear   unfortunately that isn't what I'm looking for...
 
To be precise I already implemented that kind of slicers (modified to match my needs) and they work just fine. 
I can't insert pictures here for whatever reason so I'll try describe my issue as detailed as possible:
 
Like said I've already implemented that feature in my pie charts and it works. Therefore I used the following code:
"
Amount Sales Docs& Net Value =
SWITCH(TRUE(),
VALUES('Measure Dimensions'[Measure])= "Count", [C Amount of Sales Orders],
VALUES('Measure Dimensions'[Measure])= "Local Currency", [LC Net Value],
Values('Measure Dimensions'[Measure])= "Euro Value", [€ Net Value],
BLANK())"
 
However, I need the same pie chart with the one difference of it having a small box on the bottom left corner that says: "Displayed Unit: Pieces of/Euro/[Local Currency". 
I've already made a measure that does this and tested it in a card. So when the slicer is on "Euro" It says "Euro", when its on "Count" it says "Pieces of" etc. I called the measure "Unit display".
 
Now, I somehow need to merge that measure "Unit Display" with the measure "Amount of Sales Docs. and Net Value", so that it tells me the Unit in my pie chart. I can't just put the box over the visual, cause if some looks at it in "Focus mode" then, it will disappear.
 
I tried achieving my goal using the measure:
"Amount Sales Docs& Net Value =
SWITCH(TRUE(),
VALUES('Measure Dimensions'[Measure])= "Count", CONCATENATE([Unit Display],[C Amount of Sales Orders]),
VALUES('Measure Dimensions'[Measure])= "Local Currency", CONCATENATE([Unit Display], [LC Net Value]),
Values('Measure Dimensions'[Measure])= "Euro Value", CONCATENATE([Unit Display],[€ Net Value]),
BLANK())"
 
but that didn't work and now I'm trying to overcome that hurden...

Hi @Anonymous ,

Could you please provide the formula of measure [Unit Display]? And what's data type of return values? 

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Dear @v-yiruan-msft

Measure of "Unit display" is:

Unit Display =
    Concatenate("Displayed Unit: ",
    SWITCH(TRUE(),
    VALUES('Measure Dimensions'[Measure])= "Count", "Pieces",
    VALUES('Measure Dimensions'[Measure])= "Local Currency", [Currency display],
VALUES('Measure Dimensions'[Measure])= "Euro Value", "Euro"))
 
Measure of "Currency display" is:
Currency display = MAX(TG_to_Currency[Display Currency])
 
and table "TG_to_Currency" is a list with all Subsiduaries included and the currencies they're using. "Display Currency" is formatted as text.
 

Hi @Anonymous ,

Please update the formula of measure [] as below and check whether it can get the correct result:

Amount Sales Docs& Net Value =
SWITCH (
    TRUE (),
    VALUES ( 'Measure Dimensions'[Measure] ) = "Count", CONCATENATE ( "Pieces of ", [C Amount of Sales Orders] ),
    VALUES ( 'Measure Dimensions'[Measure] ) = "Local Currency", CONCATENATE ( "Local Currency ", [LC Net Value] ),
    VALUES ( 'Measure Dimensions'[Measure] ) = "Euro Value", CONCATENATE ( "Euro ", [€ Net Value] ),
    BLANK ()
)

If still not working, could you please provide your sample pbix file if it is convenient in order to make troubleshooting and find the solution? Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Dear @v-yiruan-msft first of thanks for your support.

I changed your formula to 

 

Amount Sales Docs& Net Value =
SWITCH (
TRUE (),
VALUES ( 'Measure Dimensions'[Measure] ) = "Count", CONCATENATE ( "Pieces of ", [C Amount of Sales Orders] ),
VALUES ( 'Measure Dimensions'[Measure] ) = "Local Currency", CONCATENATE ("[Currency display]", [LC Net Value] ),
VALUES ( 'Measure Dimensions'[Measure] ) = "Euro Value", CONCATENATE ( "Euro ", [€ Net Value] ),
BLANK ()
)
 
As you can see I changed "Local Currency" with the measure "Currency display" so it doesn't show "Local Currency" as a fixed expression but the Currency which is currently displayed. That is important, as the displayed currency changes, depending on the slicer-selection of the Subsiduary.
 
Anyway, that just for you to know cause it doesn't work anyway. Using this measure, all visuals get blank ("No data to be displayed")...
 
Unfortunately also I can't share my report, just screenshots as data is confidential on one hand and at the other hand I can't attach a .pbix file with my current roles in this forum...

Hi @Anonymous ,

It is a little difficult to find the cause of the problem and solution without sample data and pbix file. In this case, you may need to check which measure cannot get the value one by one. First check whether these measures([C Amount of Sales Orders], [LC Net Value] and [€ Net Value] ) can get the value, and then see whether the measure [Currency display] can get the value. In addition, can you confirm whether your Measure Dimensions table does have the following values(Count, Local Currency and Euro Value)?

yingyinr_1-1614161169250.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-yiruan-msft 

 

Oh yeah it all works:

114.PNG

(Dont worry about the numbers- they're correct)

 

It's just when I put it all together that it doesn't work... so I guess its about the CONCATENATE-function?!

 

ALso my slicer works fine as well, the table looks exactly as yours, just with an additional column "Index" (1, 2, 3) to display the slicer values ine the order I want 

Hi @Anonymous ,

Based on all the information you currently provide, everything seems to be OK. I also created these tables and measures based on your information to simulate your scenario, and they all work normally after testing. Not sure why you can't get the value in your scenario...

test result.JPG

Please try to modify your measure and replace CONCATENATE with "&" and check whether it can work or not. If it still not working, please change the measures [C Amount of Sales Orders],[LC Net Value] and [€ Net Value] as static values and check whether it can return any value just like below screenshot...

Amount Sales Docs& Net Value =
SWITCH (
    TRUE (),
    VALUES ( 'Measure Dimensions'[Measure] ) = "Count", [Unit Display] & [C Amount of Sales Orders],
    VALUES ( 'Measure Dimensions'[Measure] ) = "Local Currency", [Unit Display] & [LC Net Value],
    VALUES ( 'Measure Dimensions'[Measure] ) = "Euro Value", [Unit Display] & [€ Net Value],
    BLANK ()
)

change measure with static values.JPG

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Dear @v-yiruan-msft I modified it according to your instructions.

It seems to be like following:

It works without problems for Card Visuals

If you use other visuals, like pie charts, it doesn't work. Unfortunately I am required to do the second. Should have mentioned that earlier, my bad.

I think there is no way it will work there is it?

Hi @Anonymous ,

How did you create the pie chart? Which table field or measure the Legend and Values field come from respectively? 

pie chart.JPG

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.