Forum Discussion
Dynamic text card visual based on filter selection
Hi All,
Please check the link below for my query.
Please download the pbi file from the below link.
https://drive.google.com/file/d/1F2LYm6qX6it6eq1jDkASYBRZtFvZ6tme/view?usp=sharing
5 Replies
- dk_dkSuper User
In Scenario 2 do you always compare the growth to July and August 2022?
- JayaselvanHelper II
No it will be dynamic, If you see the month filter, I have selected Sep-2022. In that case it should compare with Aug-2022(previous month) and July-2022(before 2 months).
- dk_dkSuper User
Hi Jayaselvan
I was able to make the logic for scenario 2, where there is a single country selected. I don't have any more time today to work on scenario 1, but maybe someone else can help you with it or otherwise I can look at it later.
You can create a measure like this:CardVisual Text = //single or multi select VAR singlecountry = HASONEVALUE(Customer[country]) // single country scenario VAR SelectedCountry = SELECTEDVALUE(Customer[country]) // selected month VAR slicerSelection = MAX(Orders[Tr_Mn_Yr]) //Previous month formatted as text VAR PrevMonth = SWITCH(MONTH(DATEVALUE(slicerSelection)-28),1,"Jan",2,"Feb",3,"Mar",4,"Apr",5,"May",6,"Jun",7,"Jul",8,"Aug",9,"Sep",10,"Oct",11,"Nov",12,"Dec") VAR PrevYear = YEAR(DATEVALUE(slicerSelection)-28) VAR slicerSelectionpr1= PrevMonth&" "&PrevYear //2 months ago formatted as text VAR PrevMonth2 = SWITCH(MONTH(DATEVALUE(slicerSelection)-56),1,"Jan",2,"Feb",3,"Mar",4,"Apr",5,"May",6,"Jun",7,"Jul",8,"Aug",9,"Sep",10,"Oct",11,"Nov",12,"Dec") VAR PrevYear2 = YEAR(DATEVALUE(slicerSelection)-56) VAR slicerSelectionpr2= PrevMonth2&" "&PrevYear2 //revenue in selected country last month VAR selCountryPrev = CALCULATE(SUM(Orders_Product_Level[Revenue]),Customer[country]=SelectedCountry,Orders[Tr_Mn_Yr] = slicerSelectionpr1) //revenue in selected country 2 months ago VAR selCountryPrev2 = CALCULATE(SUM(Orders_Product_Level[Revenue]),Customer[country]=SelectedCountry,Orders[Tr_Mn_Yr] = slicerSelectionpr2) //revenue in selected country in selected month VAR selCountryCurrent = CALCULATE(SUM(Orders_Product_Level[Revenue]),Customer[country]=SelectedCountry,Orders[Tr_Mn_Yr] = slicerSelection) //growth claculation logic VAR growthSincePrev2 = ROUND(((selCountryCurrent-selCountryPrev2)/selCountryPrev2)*100,2) VAR prev2change = IF(growthSincePrev2>0,"increased","decreased") VAR growthSincePrev = ROUND(((selCountryCurrent-selCountryPrev)/selCountryPrev)*100,2) VAR prevchange = IF(growthSincePrev>0,"increased","decreased") //text concatenation VAR singleCountryText = "The " & SelectedCountry & " has " & prevchange & " by " & growthSincePrev & "% when compared to " & slicerSelectionpr1 & ", and " & prev2change & " by " & growthSincePrev2 & "% when compared to " & slicerSelectionpr2 &"." // multiple countries TO BE FINISHED VAR RevenueCurrent = CALCULATE(SUM(Orders_Product_Level[Revenue]),Orders[Tr_Mn_Yr]=slicerSelection) RETURN // if single selection show the text output for that, else show placeholder IF(singlecountry,singleCountryText,"Multiple selections logic to be done")and add the measure to a card visual.
If multiple values are selected it will show a placeholder text, if a single country then it will show the described scenario. See my examples:
UK, Sept
USA, Nov:
I hope this helps.
- JayaselvanHelper II
Please help!