Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
@Anonymous , Please follow the approch of measure slicer and change your selection based on that.
refer
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
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
Dear @v-yiruan-msft
Measure of "Unit display" is:
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
Dear @v-yiruan-msft first of thanks for your support.
I changed your formula to
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)?
Best Regards
Oh yeah it all works:
(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...
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 () ) |
Best Regards
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?
Best Regards
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |