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
HourGlass
Frequent Visitor

Additional columns OR Icons on the latest month >> in a Matrix

I have a basic start schema where to my Fact table I have got few other Dimension tables attached. One of these dimension tables is DimDate. I have tested and all seems to be working correctly. 

 

I have created a matrix like below where I have my:

DateDimension (In Green) as Columns

Measure [Sales Growth] (In Orange) as Values

Values from other dimensions (in Blue).

 

Percentage growth of mobile phones:

BrandTypeModelJan-23Feb-23Mar-23Diff in 2 monhtsTrend Icon
SamsungBrickGalaxy S2367%23%76%53%Up
NokiaFlipN9045%87%23%-64%Down
MicrosoftBookSurface87%29%59%30%Up
AppleBrickiPhone 1323%98%37%-61%Down

 

I have so far been able to creat the measuree "Diff in 2 months" =

 

VAR _MaxMonth = CALCULATE(MAX(DimDate[YearMonthNumber]), ALLSELECTED(DimDate))
VAR _LatestAct = 
CALCULATE([Sales Growth], 
    FILTER(DimDate, 
    DimDate[YearMonthNumber] = _MaxMonth)
)

VAR _PrevMaxMonth = CALCULATE(MAX(DimDate[YearMonthNumber]), ALLSELECTED(DimDate), DimDate[YearMonthNumber] < _MaxMonth)
VAR _PrevLatestAct = 
CALCULATE([Sales Growth], 
    FILTER(DimDate, 
    DimDate[YearMonthNumber] = _PrevMaxMonth)
)

VAR _diff = _LatestAct - _PrevLatestAct

RETURN
_diff 

 

 

How do I use the measure in the matrix? 

 

Once I have the firstcolumn added I think I sould be able to add the same column and then use Conditional Formating > Icons to get the KPI Icons. 

 

 

Now, I have two options:

OPTION 1:

I would like to have the columns (in Purple) aftert the dates:

Diff in 2 months: while gives me the difference between Mar 2023 and Feb 2023 

Trend Icon: A KPI icon instead of Up and Down.

 

OPTION 2:
If having those additional columns is not possible then I could just use the Conditional Formattting > ICONS on the lastest month. But I can't figute out how I can only show Icons working for the last month. What would have worked is if I could have a column right after the latest month which represents all the months then the Icons would have worked, I think.

9 REPLIES 9
Greg_Deckler
Super User
Super User

@HourGlass Can you post some sample data (raw data)?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks for replying @Greg_Deckler but I have made an MVP of the solution and this not hte original data.

Could I mock something up for you? What was it that you needed to see from the raw data and I can try to mock it up, maybe?

@HourGlass Yeah, if you have an MVP of the situation maybe just share out a link to the PBIX.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hey @Greg_Deckler , have you seen the MVP and also an example by @lbendlin?

@HourGlass I guess I just assumed @lbendlin had solved it. Is there an issue with what @lbendlin did?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Believe @lbendlin was replyign to you when you asked for a MVP.

Anyways, so the problem is that I need the "Trend Icon" column at the end and not for everymonth. Please refer to the MVP file.  This "Trend Icon" column ideally should dispaly the the Icon (Up or Down) depending on the results of hte valus of the last months. 

 

Please refer to the original post for a bit more detail.

Hi @Greg_Deckler 

 

I originally just mocked-up the result but now I have created an actual MVP.

 

Please have a look:

https://mega.nz/file/eUYgDD4K#1jHGZlcWEcVjRCssIDjNnz0y_nicPO3x_4d9kfJZfBU

 

Thanks

lbendlin_0-1679770435885.png

 

see attached. What needs to be changed?

 

Hi @Greg_Deckler  & @lbendlin Iben

 

I take it that it is safe to say that this solution is no longer possible.

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.